Prerequisites

• Basic knowledge of databases
• Basic knowledge of SQL
• Basic knowledge of the relational database model
• Basic knowledge of the healthcare industry

SQL stands for Structured Query Language and it is the standard language to extract, change, and remove data (amongst other things) from a relational database management system (RDBMS). If you are a SQL beginner, click here to learn the basics before proceeding. In that article, you will learn to construct a basic SQL query, update statements, delete statements, and explore SQL operators. You will need to be familiar with the building blocks of SQL before learning intermediate SQL in this article.

Specifically, you should know how to write SELECT, FROM, and WHERE clauses for your SQL queries as well as how to leverage logical, comparison (relational), and arithmetic operators. You should also know how to update and delete from a relational database table with SQL. These concepts will be applied repeatedly if you are doing any significant work with healthcare relational databases.

Insert Statements

A SQL insert statement allows new data to be written to a specified table in your relational database. Insert statements typically take one of two forms (using the VALUES keyword or using the SELECT keyword). Consider the following examples.

Using Values

INSERT INTO PATIENTS(PATIENT_ID,FIRST_NAME,LAST_NAME)
VALUES(4,'Micky','Mouse');

Above, notice the use of the INSERT INTO and VALUES keywords. The destination table always follows INSERT INTO (PATIENTS in this case). The values in between the parentheses represent the columns of the table that you are inserting into (PATIENT_ID, FIRST_NAME, and LAST_NAME are all columns of PATIENTS).

While you can include every column of the table, this is not required. You can simply include a subset of columns. However, any constraints on the table will be enforced when the insert is executed. For example, if you try to do an INSERT INTO and do not include the primary key of the table in the statement then a database error will be thrown. In the above, PATIENT_ID is the primary key of PATIENTS so I must include PATIENT_ID in my INSERT INTO statement.

Similarly, a set of parentheses follow the VALUES keyword. These are the values that correspond to the columns outlined after the table name. Meaning that 4 is the value being inserted as the PATIENT_ID, ‘Micky’ is the value being inserted as the FIRST_NAME, and so on. Moreover, if the number of values does not match the number of columns in the insert statement, then the transaction will error.

Using Select

Consider a second example that does not use the VALUES keyword.

INSERT INTO PATIENTS(PATIENT_ID,FIRST_NAME,LAST_NAME)
(SELECT * FROM PATIENTS_BACKUP WHERE ADMIT_TYPE = 'I');

Here, instead of selecting the values individually, the values to insert are gathered via a query. The advantage of this insertion method is that it allows you to insert many records (rows) all at once. This cannot be done when using the VALUES keyword. For this reason, you should be using a query to perform SQL inserts most of the time. However, this obviously depends on what you are trying to accomplish.

SQL Joins

A SQL join is when two tables are combined via a query based on a foreign key relationship. Consider the following two tables, PATIENTS and LAB.

PATIENTS

PATIENT_ID	FIRST_NAME	LAST_NAME
1	Fannie	Mae
2	Freddie	Mac
3	Bob	Jones

LAB

LAB_ID	PATIENT_ID	OBS_DATE	OBSERVATION	VALUE
1	1	05/12/2021	CBC	4.35-5.65 trillion cells/L
2	1	01/01/1999	Lipid Panel	100 mg/dL
3	2	08/02/1985	Hemoglobin A1C	4.5%
4	4	08/08/1965	Hemoglobin A1C	5.0%

Notice that the first two rows in LAB are for patient ‘Fannie Mae’ (PATIENT_ID 1). Say that you only wanted to query labs for Fannie. A SQL join would be the only way to get this specific data out of the database. There are four main ways you could join PATIENTS and LAB. These include:

• Inner Join
• Left Join
• Right Join
• Full Outer Join

Inner Join

An inner join only returns the rows that are common to both tables based on the certain foreign key relationship. Consider the following inner join with the PATIENTS and LAB tables.

SELECT P.PATIENT_ID, P.FIRST_NAME, P.LAST_NAME, L.OBSERVATION, L.VALUE
FROM PATIENTS P INNER JOIN LAB L ON P.PATIENT_ID = L.PATIENT_ID;

Notice the new keywords INNER JOIN and ON. To perform an inner join, you place one table to the left of INNER JOIN and the other to the right of INNER JOIN. Next, the ON keyword is followed by the foreign key relationship. In the example above, the join condition is only satisfied where the PATIENT_ID in PATIENTS equals PATIENT_ID in LAB. This means that the PATIENT_ID has to be found in both PATIENTS and LAB for the row to appear in the result set.

Also, notice the “P” and “L” after PATIENTS and LAB, respectively. These are aliases that allow you to reference columns in the tables succinctly (it would be a pain if you had to spell out PATIENTS or LAB every time you wanted to reference a table). These aliases are the only way that a database can distinguish between PATIENT_ID in PATIENTS and PATIENT_ID in LAB so it’s important to get used to leveraging aliases in you queries. See the results of the inner join below.

PATIENT_ID	FIRST_NAME	LAST_NAME	OBSERVATION	VALUE
1	Fannie	Mae	CBC	4.35-5.65 trillion cells/L
1	Fannie	Mae	Lipid Panel	100 mg/dL
2	Freddie	Mac	Hemoglobin A1C	4.5%

Left Join

A left join returns all rows in the first table (even if they are not found in the second table based on the foreign key relationship); however, the second table’s rows that are not common to both tables are not returned. Consider the following example with the PATIENTS and LAB tables.

SELECT P.PATIENT_ID, P.FIRST_NAME, P.LAST_NAME, L.OBSERVATION, L.VALUE
FROM PATIENTS P LEFT JOIN LAB L ON P.PATIENT_ID = L.PATIENT_ID;

See the results of the left join below.

PATIENT_ID	FIRST_NAME	LAST_NAME	OBSERVATION	VALUE
1	Fannie	Mae	CBC	4.35-5.65 trillion cells/L
1	Fannie	Mae	Lipid Panel	100 mg/dL
2	Freddie	Mac	Hemoglobin A1C	4.5%
3	Bob	Jones	Null	Null

Notice that Bob Jones does not have any labs but is still returned in the left join. This is why OBSERVATION and VALUE are Null. There are no OBSERVATION and VALUE data to pull for Bob Jones in LAB.

Right Join

A right join returns all rows in the second table (even if they are not found in the first table based on the foreign key relationship); however, the first table’s rows that are not common to both tables will not be returned. Consider the following example with the PATIENTS and LAB tables.

SELECT P.PATIENT_ID, P.FIRST_NAME, P.LAST_NAME, L.OBSERVATION, L.VALUE
FROM PATIENTS P LEFT JOIN LAB L ON P.PATIENT_ID = L.PATIENT_ID;

See the results of the right join below.

PATIENT_ID	FIRST_NAME	LAST_NAME	OBSERVATION	VALUE
1	Fannie	Mae	CBC	4.35-5.65 trillion cells/L
1	Fannie	Mae	Lipid Panel	100 mg/dL
2	Freddie	Mac	Hemoglobin A1C	4.5%
4	Null	Null	Hemoglobin A1C	5%

Notice that the patient with PATIENT_ID 4 is not found in PATIENTS but still appears in the result set. This is why FIRST_NAME and LAST_NAME are Null. There is no FIRST_NAME or LAST_NAME data to pull for patient with PATIENT_ID 4.

Full Outer Join

A full outer join returns all matching rows regardless if the table A row has a corresponding value in table B or if the table B row has a corresponding value in table A. Table A and table B being the first and second tables mentioned in the query, respectively. Consider the following example.

SELECT P.PATIENT_ID, P.FIRST_NAME, P.LAST_NAME, L.OBSERVATION, L.VALUE
FROM PATIENTS P FULL OUTER JOIN LAB L ON P.PATIENT_ID = L.PATIENT_ID;

Notice the use of the FULL OUTER JOIN keywords with ON followed by the foreign key relationship as normal.

PATIENT_ID	FIRST_NAME	LAST_NAME	OBSERVATION	VALUE
1	Fannie	Mae	CBC	4.35-5.65 trillion cells/L
1	Fannie	Mae	Lipid Panel	100 mg/dL
2	Freddie	Mac	Hemoglobin A1C	4.5%
3	Bob	Jones	Null	Null
4	Null	Null	Hemoglobin A1C	5.0%

The results should look familiar. Notice that both uncommon rows are included when using the full outer join.

More SQL Operators

Bitwise Operators

Bitwise operators are actions that are performed on the data at the bit level. A bit (1 or 0) is the lowest level that a piece of data can be broken down to in a digital system.

Operator	Description	Example
|	Bitwise OR: ORs the data bits	SELECT FIRST_NAME | LAST_NAME
&	Bitwise AND: ANDs the data bits	SELECT FIRST_NAME & LAST_NAME
^	Bitwise XOR: Exclusive ORs the data bits	SELECT FIRST_NAME ^ LAST_NAME

You should rarely have to ever use bitwise operators when executing SQL; however, it is good to know that these operators exist.

Compound Operators

Compound operators are used when you need to perform an arithmetic formula that involves the current value of a column. For example, you would use a compounder operator to raise prices as a percentage for a specific medical procedure.

Operator	Description	Example
-=	Subtract equals: Sets the value equal to the current value minus the value on the right side of the operator.	SELECT PH -= 2
+=	Add equals: Sets the value equal to the current value plus the value on the right side of the operator.	SELECT += 3
/=	Divide equals: Sets the value equal to the current value divided by the value on the right side of the operator.	SELECT HEIGHT_FT / 30
*=	Multiply equals: Sets the value equal to the current value times the value on the right side of the operator.	SELECT HEIGHT_CM * 30.48
%=	Modulo equals: Sets the value equal to the current value modulo the value on the right side of the operator.	SELECT PH % 15
|*=	Bitwise OR equals: Sets the value equal to the result of the current value after its bits are OR’d with the bits of the value on the right side of the operator.	SELECT PH |*= 3
&=	Bitwise AND: Sets the value equal to the result of the current value after its bits are AND’d with the bits of the value on the right side of the operator.	SELECT PH &= 3
^-=	Bitwise exclusive equals: Sets the value equal to the result of the current value after its bits are XOR’d with the bits of the value on the right side of the operator.	SELECT PH ^-= 3

Note that you can construct perfectly good SQL without compound operators; however, compound operators allow you to write SQL more succinctly.