Question 1 of 6 Fundamentals Concept

What is the difference between INNER JOIN and LEFT JOIN in a scientific dataset?

Short answer

INNER JOIN returns matched records only. LEFT JOIN retains every row on the left and supplies NULL for missing right-side values. Both joins can multiply rows if keys are not unique.

Why

Imagine a participant table and a table of lab test results. An INNER JOIN removes participants with no laboratory record, potentially biasing an analysis of missingness. A LEFT JOIN keeps all participants, which is useful for documenting laboratory coverage, but a participant with three tests now appears three times.

Define the expected grain first: participant, visit, specimen or measurement. Enforce or verify key uniqueness where one-to-one matching is expected, then compare pre- and post-join counts and distinct participant IDs. Conditions on right-side columns in the WHERE clause can eliminate NULL-extended rows and unintentionally turn the LEFT JOIN into an inner-like filter.

Place applicable match conditions in ON, or explicitly describe why unmatched rows should be excluded. Choose the join based on the scientific denominator, not convenience.

Common mistakes

Assuming LEFT JOIN always preserves participant counts, or filtering right-table fields in WHERE without checking lost unmatched rows

The interviewer might ask next

What tests would you run before computing a subject-level adverse event rate?