JOINs

01The problem

Your list of top customers shows customer_id 17, 42, 88… The sales director doesn't know customers by number. She needs company names, and those are in a different table.

She also asks something trickier:

"Which customers signed up but have never shipped anything with us?"

Both questions need data from two tables at once.

02The concept

A JOIN combines rows from two tables where a condition matches, usually where a foreign key equals a primary key.

• INNER JOIN keeps only rows that have a match in both tables. A shipment joined to its customer.
• LEFT JOIN keeps every row from the left (first) table, and fills the right table's columns with NULL where there's no match.

Tables often share column names like customer_id, so give each table a short alias and prefix columns with it: s.customer_id, c.customer_id.

03Example

SELECT
  c.company_name,
  COUNT(*)          AS shipments,
  SUM(s.containers) AS containers
FROM shipments AS s
INNER JOIN customers AS c
  ON c.customer_id = s.customer_id
WHERE s.booking_date >= '2026-01-01'
GROUP BY c.customer_id, c.company_name
ORDER BY containers DESC
LIMIT 10;

04Walkthrough

• FROM shipments AS s starts with shipments and calls the table s.
• INNER JOIN customers AS c brings in customers as c.
• ON c.customer_id = s.customer_id is the matching rule: attach each shipment to the customer with the same ID.
• From here it's the GROUP BY you already know, but now you can show c.company_name.

We group by both c.customer_id and c.company_name. The ID guarantees two companies with the same name are never merged; the name is there so you can display it.

Now the second question. LEFT JOIN keeps every customer, even those with no shipments. For those customers, every shipment column comes back as NULL, so you can find them with IS NULL:

SELECT c.company_name, c.signup_date
FROM customers AS c
LEFT JOIN shipments AS s
  ON s.customer_id = c.customer_id
WHERE s.shipment_id IS NULL
ORDER BY c.signup_date;

These are customers the sales team should call.

You can chain joins. Each shipment has a route and a customer:

SELECT s.shipment_id, c.company_name, r.origin, r.destination, r.mode
FROM shipments AS s
JOIN customers AS c ON c.customer_id = s.customer_id
JOIN routes    AS r ON r.route_id    = s.route_id
WHERE s.booking_date = '2026-08-03';

05Practice

06Challenge

07Check your understanding