Structured query language (SQL) is a popular programming language for interacting with relational database management systems (RDBMS). SQL is a clause-based language with different clauses available for extracting vast quantities of data in seconds. To extract data accurately from an RDBMS, you must be able to write correct SQL statements. These statements are usually made up of clauses, field names, table names, and logical expressions. This short guide will reveal some of the commands, statements, and clauses you can use for efficient SQL database extraction.
1. SQL Database Extraction From a Single Table
You can use the SELECT statement with the FROM and WHERE clauses to extract data from one table.
The SELECT clause specifies the fields containing the data you want to extract or display.
For example, to select the PID, firstname, and lastname from the Patients table, you can write:
SELECT pid, firstname, lastname FROM Patients
If you want to select all the fields or columns in the Patients table without regard for the order in which they are displayed, you can write:
SELECT * FROM Patients.
Apart from the FROM clause, another commonly used clause is known as the WHERE clause. This clause allows you to specify conditions for data extraction using logical expressions.
For example, if you want to display all the records of patients who are taller than 1.5 meters, you can use the WHERE clause like this:
SELECT *
FROM Patients
WHERE height > 1.5
The WHERE clause may be used for a wide range of comparisons, not just for numeric data like in the example above.
2. Extraction From Multiple Tables
In most database applications, you need to pull out data from more than one table. This is particularly true when you are working with a fully normalized database.
One way to fetch data from many tables is to use joins.
A join allows you to select all the data in one table based on a matching condition from another table. For example, you may use a join to select all the test results of a specific patient.
There are various types of joins:
- Inner join
- Left outer join
- Right outer join
- Full outer join
The most commonly used is the inner join. This join will return rows with a match in the two input tables.
For instance, to extract the test results for a particular patient with ID number 00308567, you can use this SQL query:
SELECT p.pid, p.firstname, p.lastname, r.testdescription, r.date
FROM patient p
INNER JOIN results r
ON p.pid = r.pid
WHERE p.pid =”00308567″
Note that p and r have been used as aliases for the patient and results tables, respectively.
3. Combined Extraction
Another option to extract data from multiple tables is the UNION operator.
The UNION operator combines table data from various SELECT commands.
The syntax for the UNION operator is like this:
SELECT field1, field2, … fieldn
FROM table1
UNION
SELECT field1, field2, … fieldn
FROM table2;
Note that columns in each SELECT clause must have identical data types in the same position.
A quick example will illustrate the use of the UNION operator for extracting data from two test result tables. One is for results from a diagnostic lab, while the other is for results gathered from a radiology lab.
SELECT pid, firstname, lastname, testdate, testdescription
FROM labtest
UNION
SELECT pid, firstname, lastname, date, description
FROM radtest;
Those are some of the most widely used statements and clauses for SQL database extraction.
In practice, you will need to use these types of statements within an application or a tool that allows you to query databases and display datasets in a grid.
Want to have an expert migrate or extract data for you?
Contact MediQuant at 844.286.8683 to see a free demo of our data extraction tool and process. You can also reach us through our contact page to discuss all your data archiving and migration needs.