lesson 19 Joins & sets · intermediate

Keep unmatched rows

Keep rows even when the other table has no match.

the idea

A LEFT JOIN keeps every row of the left table. When no right-side row matches, the right-side columns are NULL. COUNT(right.key) then counts matches, giving zero for unmatched rows, and WHERE right.key IS NULL keeps only the unmatched ones.

Dataset: Retail

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

   Find products never ordered. Run this. LEFT JOIN keeps every product; lines with no match show NULL for order_item_id.

   step-1.sql Run

   sql›

   SELECT p.stock_code, p.description, i.order_item_id
   FROM products p
   LEFT JOIN order_items i ON i.stock_code = p.stock_code
   WHERE i.order_item_id IS NULL
   LIMIT 10;

2. 2

   Count products per category safely. Run this. COUNT(p.stock_code) counts matched products; an empty category shows 0, not 1.the pattern

   example.sql Run

   sql›

   SELECT c.category_id, c.category_name, COUNT(p.stock_code) AS products
   FROM categories c
   LEFT JOIN products p ON p.category_id = c.category_id
   GROUP BY c.category_id, c.category_name;

3. 3

   Compare inner join to full category list. Run this. Inner join only counts categories that have at least one product.

   step-3.sql Run

   sql›

   SELECT
     (
       SELECT COUNT(*)
       FROM categories
     ) AS categories,
     (
       SELECT COUNT(DISTINCT c.category_id)
       FROM categories c
       JOIN products p ON p.category_id = c.category_id
     ) AS categories_with_products;

common mistake

COUNT(*) after a LEFT JOIN counts the unmatched row itself, so a product with no lines would show 1 instead of 0.