Introduction to SQL

If you haven’t yet read Why learn to code?, it places SQL among the other languages. In short: the data in a clinical data warehouse (CDW) is stored in a database, and SQL is the language that pulls out what you need.

What a database looks like

A database is like a binder with several linked sheets — what we call tables, described in detail in the article on data organization. Let’s take two tables inspired by the OMOP model:

Each table has a key (here person_id) that links it to the others. Writing SQL means describing to the database what you want to see from these tables.

The four basic moves

SELECT and FROM — choose the columns of a table

SELECT states which columns to display, and FROM from which table. The * means “all columns”.

-- Only the identifier and the year of birth
SELECT person_id, year_of_birth
FROM person;

WHERE — filter the rows

WHERE keeps only the rows that meet a condition.

-- Female patients born before 1960
SELECT person_id, year_of_birth
FROM person
WHERE gender = 'F'
AND year_of_birth < 1960;

AND combines several conditions: a row is kept only if it meets all of them. Here, a patient must be female and born before 1960. Its counterpart OR keeps the rows that meet at least one of the conditions.

This is exactly the move you make with a filter in a spreadsheet — filtering on rows and columns — but written once, reproducibly, and applicable to millions of rows.

JOIN — link the tables

A patient’s data is spread across several tables. JOIN brings them together using the shared key.

-- Associate each creatinine measurement with the matching patient
SELECT p.person_id, p.year_of_birth, m.value, m.date
FROM person AS p
JOIN measurement AS m
ON p.person_id = m.person_id;

Here p and m are nicknames (aliases) given to the tables to shorten the writing. The condition ON p.person_id = m.person_id tells the database how to match rows from the two tables.

GROUP BY — aggregate

GROUP BY groups rows to compute statistics per group: a count, a mean, a maximum…

-- Number of measurements and mean creatinine, per patient
SELECT person_id,
     COUNT(*)    AS n_measurements,
     AVG(value)  AS mean_creatinine
FROM measurement
GROUP BY person_id;

COUNT(*) counts the rows of each group, and AVG (short for average) computes their mean. Other functions follow the same principle: MIN, MAX, SUM. The word AS gives the computed column a name.

Putting it all together. A realistic query combines these four moves. For example: “for each female patient over 65, the maximum creatinine measured in 2023”.

SELECT p.person_id,
     MAX(m.value) AS max_creatinine
FROM person AS p
JOIN measurement AS m
ON p.person_id = m.person_id
WHERE p.gender = 'F'
AND (2023 - p.year_of_birth) > 65
AND m.date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY p.person_id;

You select (SELECT), link (JOIN), filter (WHERE) and group (GROUP BY). That is the skeleton of most extractions.

Resources to learn

SQL is learned by writing queries, not by reading them. Here are two free, progressive resources to practise directly in the browser, with nothing to install.

SQL and Linkr

In Linkr, the Study Designer automatically generates the SQL matching the inclusion criteria and variables you define with the mouse. Being able to read that SQL helps you verify what the tool is doing and take back control for specific needs.