lesson 25 Joins & sets · intermediate

ON versus WHERE in a LEFT JOIN

Place filters carefully when preserving unmatched rows.

the idea

In a LEFT JOIN, a condition in ON decides which right-side rows match; unmatched left rows are still kept. The same condition in WHERE runs after the join and removes rows whose right side is NULL, turning the LEFT JOIN back into an inner join.

Dataset: Healthcare

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Put the filter in ON. Run this. LEFT JOIN with telehealth in ON keeps every provider; counts are zero when none match.

   step-1.sql Run

   sql›

   SELECT p.provider_id, COUNT(e.encounter_id) AS telehealth_visits
   FROM providers p
   LEFT JOIN encounters e
     ON e.provider_id = p.provider_id
     AND e.encounter_class = 'telehealth'
   GROUP BY p.provider_id
   ORDER BY telehealth_visits
   LIMIT 10;

2. 2

   Same filter in WHERE instead. Run this. WHERE telehealth removes providers with no matching encounter—like an inner join.

   step-2.sql Run

   sql›

   SELECT COUNT(DISTINCT p.provider_id) AS providers_left
   FROM providers p
   LEFT JOIN encounters e ON e.provider_id = p.provider_id
   WHERE e.encounter_class = 'telehealth';

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT p.provider_id, COUNT(e.encounter_id) AS telehealth_visits
   FROM providers p
   LEFT JOIN encounters e
     ON e.provider_id = p.provider_id
     AND e.encounter_class = 'telehealth'
   GROUP BY p.provider_id;

common mistake

LEFT JOIN encounters e ON … WHERE e.encounter_class = 'emergency' drops every provider without an emergency visit.