lesson 18 Joins & sets · intermediate

Connect tables with JOIN

Connect rows from related tables using matching keys.

the idea

A JOIN combines rows from two tables whose key values match. Write the relationship in ON: orders.customer_id = customers.customer_id. An inner join keeps only rows that find a match, and each output row is one matched pair.

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

   See customer_id on each order. Run this. customer_id on orders matches customers.customer_id on the other table.

   step-1.sql Run

   sql›

   SELECT order_id, customer_id
   FROM orders
   WHERE ordered_at >= '2011-12-09'
   LIMIT 10;

2. 2

   Join orders to customers. Run this. JOIN attaches each order to its customer row so you can read region.the pattern

   example.sql Run

   sql›

   SELECT o.order_id, o.invoice_no, c.region
   FROM orders o
   JOIN customers c ON c.customer_id = o.customer_id
   WHERE o.ordered_at >= '2011-12-09';

common mistake

Anonymous orders have customer_id NULL, so an inner join to customers silently drops them. That is correct only if the question is about identified customers.