SQL Essentials for Clinical Data Management

Why this matters

Most CDM questions can be answered with a few SQL building blocks—SELECT, WHERE, and JOIN—if you respect row grain (what one row stands for) and join cardinality (how many rows should appear after a join). Getting those two wrong quietly changes counts, hides missing data, and leads to incorrect listings, key performance indicators (KPIs), and lock decisions.

In data management SQL is for reading (listings, checks, reconciliation), with read‑only access and, where possible, on a reporting copy. A finding in site‑entered data goes to the site as a data query (raised in the electronic data capture system, not an SQL query) and the site corrects or confirms its own data; a vendor corrects and re‑sends its own. Nobody fixes data with an ad hoc UPDATE or DELETE.

Core concepts & definitions

• Row grain The “unit” represented by each row (for example: one row per participant, or per participant‑visit, or per lab test result). Always name it.

• Width vs rows

  • SELECT changes width (which columns appear).
  • WHERE changes rows (which records remain). Selecting fewer columns does not reduce row count on its own.

• Other row‑changing clauses SELECT DISTINCT removes duplicate rows; GROUP BY returns one row per group (HAVING filters the groups, WHERE filters rows before grouping); an aggregate with no GROUP BY returns one row.

• Join keys Columns used to match rows across tables. Keys must reflect the true grain (often a composite like participant_id + visit_name + parameter).

• One‑to‑many If you join a parent table (one row per participant) to a child table (many rows per participant: one per participant‑visit‑parameter), the result inherits the child grain.

• NULL behavior A comparison with NULL (=, <>, >, <) is unknown, not true, and WHERE keeps only rows where the condition is true: a NULL row passes neither x > 9000 nor x <= 9000, and x = NULL matches nothing. To test for missingness, use IS NULL or IS NOT NULL. Use COALESCE() if you need a fallback value. IS NULL finds an empty value in an existing row; a record never entered has no row and needs an anti‑join (example 4).

• INNER vs LEFT join

  • INNER JOIN: keep only records with a match on both sides.
  • LEFT JOIN: keep all left‑side records; fill the right‑side columns with NULL when there’s no match.

The process, step‑by‑step

1. State the question and grain in plain language Example: “List vitals, one row per participant‑visit‑parameter, and include lab glucose if collected the same day.”

2. Pick columns on purpose (SELECT) Include only columns needed to answer the question. Remember: this changes width, not row count.

3. Filter carefully (WHERE)

   • Be explicit about date ranges.
   • Use IS NULL / IS NOT NULL for missing data.
   • Remember that > excludes the boundary; use >= or <= when you intend to include it.

4. Design the join

   • Identify parent vs child tables.
   • List the exact join keys (composites when needed).
   • Choose INNER when a match is required; choose LEFT to keep unmatched left rows.

5. Verify cardinality Run quick checks before and after joins:

   -- Before joining
   SELECT COUNT(*) AS n_vitals, COUNT(DISTINCT participant_id) AS n_participants FROM vitals;
   
   -- After joining to participants (joined_view = your join); expect the same numbers
   SELECT COUNT(*) AS n_rows, COUNT(DISTINCT participant_id) AS n_participants FROM joined_view;
   

6. Sanity‑check outputs Spot duplicates, unexpected drops/spikes, and impossible values (e.g., negative heart rate).

What good looks like

• A top‑of‑query comment that states grain, population, time window, and assumptions.
• Joins on the correct composite key, not just a single ID.
• Post‑join checks: COUNT(*), COUNT(DISTINCT key), and a quick duplicate scan.
• Deliberate NULL handling (e.g., using IS NULL to count missing lab results).
• Predictable date filters (visit_date BETWEEN DATE '2025-01-01' AND DATE '2025-06-30'). BETWEEN includes both ends; on a date‑time column it cuts off at the start of 30 June, so end with < DATE '2025-07-01' there.

Worked examples

All examples are self‑contained with tiny sample datasets (MRN, the medical record number, is a practice column: sponsor databases should hold no direct identifiers). You can paste them into a scratch database (never a study database) to practice.

Sample data

-- Participants (one row per participant)
CREATE TABLE participants (
  participant_id TEXT PRIMARY KEY,
  sex TEXT,
  arm TEXT,
  medical_record_number INTEGER  -- may be NULL
);

INSERT INTO participants VALUES
('P001','F','A', 9050),
('P002','M','B', NULL),
('P003','F','A', 8999);

-- Vitals (one row per participant-visit-parameter)
CREATE TABLE vitals (
  participant_id TEXT,
  visit_name TEXT,
  collected_date DATE,
  parameter TEXT,     -- e.g., 'HR' (heart rate), 'SBP'
  value NUMERIC,
  unit TEXT
);

INSERT INTO vitals VALUES
('P001','Screening','2025-02-01','HR',72,'bpm'),
('P001','Screening','2025-02-01','SBP',118,'mmHg'),
('P001','Week 4'   ,'2025-03-01','HR',70,'bpm'),
('P002','Screening','2025-02-02','HR',80,'bpm');

-- Labs (one row per participant-collection-test)
CREATE TABLE labs (
  participant_id TEXT,
  collected_date DATE,
  test_name TEXT,     -- e.g., 'Glucose'
  result_value NUMERIC,
  unit TEXT,
  status TEXT         -- e.g., 'Final', 'Canceled'
);

INSERT INTO labs VALUES
('P001','2025-02-01','Glucose', 92,'mg/dL','Final'),
('P001','2025-03-01','Glucose', 95,'mg/dL','Final'),
('P003','2025-02-01','Glucose', NULL,'mg/dL','Canceled');

1) Projection vs filtering

Goal: Show that selecting columns changes width, while WHERE changes rows.

-- Same rows, fewer columns (projection)
SELECT participant_id, arm
FROM participants;

-- Fewer rows because of a filter (rows)
SELECT *
FROM participants
WHERE medical_record_number > 9000;  -- excludes NULLs and numbers <= 9000

Takeaway: Only P001 is returned: P002 (NULL) is unknown and is left out here and by <= 9000 alike. If you need “participants missing MRN,” ask for them explicitly:

SELECT *
FROM participants
WHERE medical_record_number IS NULL;

2) One‑to‑many join (parent → child)

Goal: Get vitals with participant attributes. Result grain becomes participant‑visit‑parameter.

/* Grain: one row per participant-visit-parameter (vitals) */
SELECT v.participant_id, v.visit_name, v.collected_date,
       v.parameter, v.value, v.unit,
       p.sex, p.arm
FROM vitals v
LEFT JOIN participants p
  ON p.participant_id = v.participant_id;

Why LEFT? We want to keep every vitals row, even one with no matching participant.

Sanity check:

SELECT COUNT(*) AS rows_after_join FROM (
  SELECT 1
  FROM vitals v
  LEFT JOIN participants p ON p.participant_id = v.participant_id
) x;

Expect exactly as many rows as in vitals (4 here): participant_id is unique in participants. More rows mean duplicates in participants at the join key—a finding to investigate.

3) Intersection on multiple keys (day‑aligned vitals + glucose)

Goal: Keep only rows where a same‑day glucose lab exists for a vital.

/* Grain: participant-visit-parameter where a same-day Glucose result exists */
SELECT v.participant_id, v.visit_name, v.collected_date,
       v.parameter, v.value AS vital_value,
       l.result_value AS glucose_mg_dl
FROM vitals v
INNER JOIN labs l
  ON l.participant_id = v.participant_id
 AND l.collected_date = v.collected_date
 AND l.test_name = 'Glucose'
 AND l.status = 'Final';

What to expect with our sample:

• P001 Screening HR/SBP on 2025‑02‑01 will match Glucose 92.
• P001 Week 4 HR on 2025‑03‑01 will match Glucose 95.
• P002 Screening HR has no matching lab row → excluded by INNER JOIN.
• Result: 3 rows, with Glucose 92 on two of them: do not count or average glucose from this result.

4) Anti‑join (find vitals missing a same‑day glucose)

Goal: List vitals that do not have a same‑day final glucose result.

/* Grain: participant-visit-parameter with no same-day Glucose (Final) */
SELECT v.*
FROM vitals v
LEFT JOIN labs l
  ON l.participant_id = v.participant_id
 AND l.collected_date = v.collected_date
 AND l.test_name = 'Glucose'
 AND l.status = 'Final'
WHERE l.participant_id IS NULL;  -- no match found

Keep the lab conditions in ON: in WHERE they are unknown for unmatched rows (their lab columns are NULL), so the LEFT JOIN acts as an INNER JOIN and the list comes back empty.

What to expect with our sample:

• P002 Screening HR appears (no same‑day glucose).
• P001 rows are excluded (they had same‑day glucose).
• NOT EXISTS with the same conditions returns the same row. Avoid NOT IN with a subquery: one NULL in its result and no rows come back.

5) Counting safely with NULLs and duplicates

Goal: Show the difference between COUNT(*) and COUNT(column), and how to detect duplicate keys.

-- Count all rows vs. only non-NULL MRNs
SELECT
  COUNT(*)                    AS n_participants,          -- counts all rows
  COUNT(medical_record_number) AS n_with_mrn,             -- skips NULL
  COUNT(DISTINCT participant_id) AS n_unique_participants
FROM participants;

-- Detect duplicate rows in vitals at the grain we expect to be unique
SELECT participant_id, visit_name, collected_date, parameter,
       COUNT(*) AS dup_count
FROM vitals
GROUP BY participant_id, visit_name, collected_date, parameter
HAVING COUNT(*) > 1;

What to expect with our sample: 3, 2 and 3; the duplicate check returns no rows.

Pitfalls & misconceptions

• “SELECT less to get fewer rows.” False. A plain SELECT list changes columns (width), not row count. Use WHERE to change rows.
• “Participants (demographics) INNER JOIN vitals returns one row per participant.” False. The result takes the child grain—here, vitals—and participants with no vitals (P003) drop out unless you LEFT JOIN from participants.
• Using partial keys. If the true match needs participant_id + collected_date + parameter, do not join on participant_id alone.
• Forgetting NULL semantics. >, <, =, <> are never true for NULL, so status <> 'Final' skips rows with no status. Use IS NULL or IS NOT NULL for missingness.
• Boundary confusion. > '2025-01-01' excludes '2025-01-01'. Use >= when you mean to include it.
• Silent row multiplication. If a side has several rows per join key, the other side’s rows repeat: vitals joined to labs on participant_id alone gives P001 6 rows, not 3. Always check duplicates at the intended grain; never hide them with DISTINCT.

Practice in the wild (checklist)

• Write the intended row grain in a comment at the top of every query.
• List the exact join keys; confirm they represent the intended grain.
• After joins, check COUNT(*), COUNT(DISTINCT key), and scan for duplicates.
• Handle missingness deliberately (IS NULL, IS NOT NULL, COALESCE()).
• Make date boundaries explicit and inclusive/exclusive as intended.
• Prefer LEFT JOIN when you must keep the left population intact.

Key points to remember

• Always state the row grain. Say what one row represents and keep every choice (join keys, filters, checks) aligned to that grain.
• Projection vs. rows. SELECT changes which columns you show (width); WHERE changes which records you keep (rows); DISTINCT, GROUP BY and aggregates change the row count too.
• Choose join type on purpose. Use INNER when a match is required; use LEFT to preserve the left‑side population and surface missingness. For “missing match” lists, use a LEFT + WHERE right.key IS NULL anti‑join (or NOT EXISTS), keeping the match conditions in ON.
• Define the real composite key. Join on the exact fields that identify the intended grain (often multi‑column). Under‑keying silently multiplies rows.
• Child grain wins. In one‑to‑many joins, the result inherits the more granular table’s grain—plan your counts and grouping accordingly.
• Verify cardinality after every join. Check COUNT(*), COUNT(DISTINCT <grain key>), and scan for duplicate keys to catch row drops or explosions early.
• Handle NULLs explicitly. Comparisons with NULL (=, <>, >, <) are never true. Use IS NULL / IS NOT NULL, and COALESCE() only when a safe fallback is intended.
• Count carefully. COUNT(*) counts rows; COUNT(column) ignores NULL in that column, as do COUNT(DISTINCT column), SUM and AVG—use the version that matches your question.
• Be clear on boundaries. Make time windows explicit and choose inclusion deliberately (>=, <=, or BETWEEN when appropriate) to avoid off‑by‑one errors.
• Document assumptions. Start queries with a short comment block for grain, population, time window, and key assumptions; this prevents misinterpretation.
• Sanity‑check outputs. After filtering/joining, look for unexpected row changes, duplicate keys, and impossible values before sharing results.
• Read, never write. Use SQL read‑only; findings become data queries to the site (or a vendor correction), never an ad hoc UPDATE or DELETE. Save, review and version any SQL query behind a decision.