SQL Basics

A reactive walkthrough of SQL queries

Piyush Chaudhari

2026-09-25

Learning SQL with OMOP

Every OMOP extract uses the same tables and column names, so a query written here can run for any OMOP dataset at any insitution.

  • Clinical facts live in event tables
  • Their meaning lives in concept, the vocabulary table

Most of the queries that we will see, you will -

  1. filter an event table,
  2. join to person,
  3. join to concept to get readable names

erDiagram
    PERSON ||--o{ VISIT_OCCURRENCE : has
    PERSON ||--o{ CONDITION_OCCURRENCE : has
    PERSON ||--o{ DRUG_EXPOSURE : has
    PERSON ||--o{ MEASUREMENT : has
    VISIT_OCCURRENCE ||--o{ CONDITION_OCCURRENCE : "recorded during"
    CONCEPT ||--o{ CONDITION_OCCURRENCE : "condition_concept_id"
    CONCEPT ||--o{ DRUG_EXPOSURE : "drug_concept_id"
    CONCEPT ||--o{ MEASUREMENT : "measurement_concept_id"
    PERSON {
        int person_id PK
        int gender_concept_id FK
        int year_of_birth
    }
    CONDITION_OCCURRENCE {
        int person_id FK
        int condition_concept_id FK
        date condition_start_date
    }
    CONCEPT {
        int concept_id PK
        string concept_name
        string domain_id
    }

The dataset

  • A synthetic OMOP CDM subset stored in a DuckDB database file, omop.duckdb

  • Concept IDs are real standard concepts (SNOMED, RxNorm, LOINC)

  • People, dates and values are simulated

  • Every chart and table in this deck is a live query against it

SQL Query Syntax

How you write it How the database runs it SELECT FROM JOIN … ON WHERE GROUP BY HAVING ORDER BY LIMIT 1 FROM 2 JOIN … ON 3 WHERE 4 GROUP BY 5 HAVING 6 SELECT 7 ORDER BY 8 LIMIT Aliases are born here, so WHERE can’t see them

SELECT and FROM

  • FROM names the table

  • SELECT picks the columns you want back

  • LIMIT keeps the preview small while you explore

  • Avoid SELECT * in real extracts since OMOP tables are wide, and naming columns documents intent

WHERE filters rows

Move the controls to play with the query. The query text and the result both update

  • BETWEEN is inclusive on both ends

  • Filtering on gender_concept_id, not a text label, is the OMOP way

ORDER BY and LIMIT

  • Without ORDER BY, row order is not guaranteed, so LIMIT alone returns an arbitrary sample

JOIN turns IDs into meaning

  • Event tables store integers

  • concept holds the names

condition_occurrence person_id 12 condition_ concept_id 201826 start 2019-03-04 concept 201826 Type 2 diabetes mellitus SNOMED · Condition ON co.condition_concept_id = c.concept_id

INNER, LEFT and ANTI JOINS

GROUP BY does aggregation

  • COUNT(*) counts records

  • COUNT(DISTINCT person_id) counts patients

In EHR data these differ a lot

HAVING filters

  • WHERE runs before grouping, on rows

  • HAVING runs after grouping, on aggregates

Use this pattern for high utilisers, patients with ≥ 2 diagnosis codes and similar phenotype rules

Dates: Visits over Time

DuckDB

date_trunc('year', d)

SQL Server

DATEFROMPARTS(YEAR(d),1,1) OR DATETRUNC(year, d)

Measurements and CASE WHEN

CASE WHEN creates a derived column row by row, which is how most lab-based phenotype flags are built

Click the Chart and Query the Selection

Age bands come from a CASE expression, then GROUP BY

Click a bar (shift-click for several) to filter the table

CTEs: build a cohort step by step

Question:

Of patients with type 2 diabetes, how many started metformin within N days of diagnosis?

Each WITH block is a named step

Read top to bottom

Beyond the basics

Pattern matching, subqueries, set operations and window functions

LIKE finds concepts by text

  • % matches any run of characters
  • _ exactly one

Try %disease, _etformin, %in%, %blood%

  • Use LIKE to find concept IDs, then filter event tables on the IDs

  • Text search misses synonyms and is slow on the full vocabulary

String Functions: CONCAT, SPLIT_PART, REGEXP_EXTRACT

  • || concatenates

  • SPLIT_PART and regexp_extract pull pieces out of a string

  • Source values in OMOP (*_source_value) are free text, so these functions do most of the cleanup.

Subqueries: IN, EXISTS and NOT EXISTS

Question:

Which patients have a condition but no record of the usual drug?

This is a classic care gap query

  • EXISTS stops at the first match, so it never duplicates rows the way a JOIN can

  • NOT IN breaks if the subquery returns a NULL; prefer NOT EXISTS

UNION, INTERSECT and EXCEPT

NULL-safe summaries: COALESCE, NULLIF, FILTER

  • race_concept_id = 0 means “no matching concept”, so the LEFT JOIN returns NULL

  • COALESCE swaps in a label

  • FILTER (WHERE …) counts a subset inside one group, turning rows into columns

  • NULLIF(x, 0) returns NULL when x is 0, so a division gives NULL instead of an error

OVER aggregates without collapsing rows

GROUP BY 3 OVER 3 3 3 3 visits → 1 row 3 visits → 3 rows, each with the count

ROW_NUMBER, RANK and DENSE_RANK

  • On ties, ROW_NUMBER still counts 1, 2, 3, RANK repeats and then skips, and DENSE_RANK repeats without skipping

  • ROW_NUMBER() … = 1 is the standard OMOP “first event per person” pattern

  • DuckDB also lets you write QUALIFY rn = 1 instead of the outer query

LEAD and LAG: time between visits

  • LEAD(x) reads the next row’s value in the window and LAG(x) reads the previous one

  • This is how readmission and gap-in-care measures are built

Window frames: running and moving values

  • The frame says which rows around the current one feed the aggregate

  • ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a 3-reading moving average

  • With only ORDER BY, the frame runs from the first row, giving a running total or max

Exercise

Write any query against person, visit_occurrence, condition_occurrence, drug_exposure, measurement or concept, then run it.

Same query, three ways

Task DuckDB (this deck) SQL Server PostgreSQL
First n rows LIMIT 10 SELECT TOP 10 … LIMIT 10
Days between dates date_diff('day', a, b) DATEDIFF(day, a, b) b - a
Truncate to year date_trunc('year', d) DATETRUNC(year, d) date_trunc('year', d)
Current date current_date CAST(GETDATE() AS date) current_date
String concat a || b CONCAT(a, b) a || b
Case-insensitive match s ILIKE '%ckd%' s LIKE '%ckd%' (default collation ignores case) s ILIKE '%ckd%'
Regex match regexp_matches(s, 'p') LIKE patterns only (REGEXP_LIKE from 2025) s ~ 'p'
Conditional count COUNT(*) FILTER (WHERE …) SUM(CASE WHEN … THEN 1 ELSE 0 END) COUNT(*) FILTER (WHERE …)
Keep first row per group QUALIFY ROW_NUMBER() OVER (…) = 1 subquery with WHERE rn = 1 subquery with WHERE rn = 1
Table reference cdm.person cdm_db.dbo.person cdm.person

OHDSI’s SqlRender translates one parameterised query into each dialect, which is how ATLAS and HADES packages stay portable

Export the result

Pipe-delimited with every field quoted survives commas inside concept names. The year leads the filename so extracts sort by period.

COPY (
  SELECT ...
) TO '2024_t2dm_cohort.tsv'
  (HEADER, DELIMITER '|', FORCE_QUOTE *);

SQL Server: bcp or sqlcmd -s"|" -W, then wrap values in quotes during the export query

Recap

FROM which table (and alias) JOIN … ON link tables, usually on person_id or *_concept_id WHERE keep rows, before any grouping GROUP BY collapse rows into groups for COUNT, AVG, MIN HAVING keep groups, after aggregation SELECT choose and compute output columns, incl. CASE WHEN ORDER BY sort, then LIMIT to trim

Recap: beyond the basics

LIKE / ILIKE match text with % and _, to find concept IDs EXISTS keep rows that have (or lack) a related row, without duplicating them UNION · INTERSECT · EXCEPT combine two result sets that have the same columns COALESCE · NULLIF replace NULLs and prevent division by zero FILTER (WHERE …) count a subset inside each group OVER (PARTITION BY …) aggregate per group and keep every row ROW_NUMBER · RANK number rows, e.g. the first event per person LAG · LEAD read the previous or next row, e.g. gaps between visits ROWS BETWEEN … running and moving aggregates

References

These slides are made using the following tools -

  • DuckDB - an in-process SQL OLAP database management system
  • Quarto - a scientific and technical publishing system built on Pandoc
  • Marimo - a Python library for interactive data exploration and visualization