Do the till and the warehouse agree?

Do the till and the warehouse agree on how many of each product we sold? New tool: FULL OUTER JOIN. A LEFT JOIN keeps everything from the left table; a FULL OUTER JOIN keeps everything from both, so a product only one system has still shows, with an empty cell on the other side. COALESCE takes the product name from whichever side has it.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

FULL OUTER JOIN: keep both sides

WITH t AS (
  SELECT product, SUM(units) AS till_units
  FROM till_sales
  GROUP BY product
), w AS (
  SELECT product, SUM(units) AS warehouse_units
  FROM warehouse_out
  GROUP BY product
)
SELECT COALESCE(t.product, w.product) AS product,
       t.till_units,
       w.warehouse_units,
       CASE
         WHEN t.till_units = w.warehouse_units THEN 'match'
         ELSE 'check'
       END AS status
FROM t
FULL OUTER JOIN w ON t.product = w.product

Result:

product	till_units	warehouse_units	status
Charger	2	1	check
Earbuds	4	4	match
Phone case	4		check
Power bank		3	check
Speaker	2	2	match

Step by step

Every product either system has.

WITH t AS (SELECT product, SUM(units) AS till_units FROM till_sales GROUP BY product), w AS (SELECT product, SUM(units) AS warehouse_units FROM warehouse_out GROUP BY product) SELECT COALESCE(t.product, w.product) AS product, t.till_units, w.warehouse_units, CASE WHEN t.till_units = w.warehouse_units THEN 'match' ELSE 'check' END AS status FROM t FULL OUTER JOIN w ON t.product = w.product

Total each first.

WITH t AS (SELECT product, SUM(units) AS till_units FROM till_sales GROUP BY product), w AS (SELECT product, SUM(units) AS warehouse_units FROM warehouse_out GROUP BY product) SELECT COALESCE(t.product, w.product) AS product, t.till_units, w.warehouse_units, CASE WHEN t.till_units = w.warehouse_units THEN 'match' ELSE 'check' END AS status FROM t FULL OUTER JOIN w ON t.product = w.product

That's what FULL OUTER JOIN is for.

WITH t AS (SELECT product, SUM(units) AS till_units FROM till_sales GROUP BY product), w AS (SELECT product, SUM(units) AS warehouse_units FROM warehouse_out GROUP BY product) SELECT COALESCE(t.product, w.product) AS product, t.till_units, w.warehouse_units, CASE WHEN t.till_units = w.warehouse_units THEN 'match' ELSE 'check' END AS status FROM t FULL OUTER JOIN w ON t.product = w.product

Two CTEs, then FULL OUTER JOIN, COALESCE for the name, CASE for match or check.

WITH t AS (SELECT product, SUM(units) AS till_units FROM till_sales GROUP BY product), w AS (SELECT product, SUM(units) AS warehouse_units FROM warehouse_out GROUP BY product) SELECT COALESCE(t.product, w.product) AS product, t.till_units, w.warehouse_units, CASE WHEN t.till_units = w.warehouse_units THEN 'match' ELSE 'check' END AS status FROM t FULL OUTER JOIN w ON t.product = w.product

Practice

Try each one in your head first, then open the answer.

1. Do the till and the warehouse agree on how many of each product we sold? Show product, till_units, warehouse_units and status (match or check).

WITH t AS (SELECT product, SUM(units) AS till_units FROM till_sales GROUP BY product), w AS (SELECT product, SUM(units) AS warehouse_units FROM warehouse_out GROUP BY product) SELECT COALESCE(t.product, w.product) AS product, t.till_units, w.warehouse_units, CASE WHEN t.till_units = w.warehouse_units THEN 'match' ELSE 'check' END AS status FROM t FULL OUTER JOIN w ON t.product = w.product

2. Which products don't match between the till and the warehouse? Show product, till_units and warehouse_units.

WITH t AS (SELECT product, SUM(units) AS till_units FROM till_sales GROUP BY product), w AS (SELECT product, SUM(units) AS warehouse_units FROM warehouse_out GROUP BY product) SELECT COALESCE(t.product, w.product) AS product, t.till_units, w.warehouse_units FROM t FULL OUTER JOIN w ON t.product = w.product WHERE t.till_units IS DISTINCT FROM w.warehouse_units

3. Which products appear in either the till or the warehouse? List each product once.

SELECT COALESCE(t.product, w.product) AS product FROM (SELECT DISTINCT product FROM till_sales) t FULL OUTER JOIN (SELECT DISTINCT product FROM warehouse_out) w ON t.product = w.product

4. Which products are on the till but not in the warehouse? Show product.

SELECT DISTINCT t.product FROM till_sales t LEFT JOIN warehouse_out w ON t.product = w.product WHERE w.product IS NULL

5. How many products match, and how many need a check? Show status and products.

WITH t AS (SELECT product, SUM(units) AS till_units FROM till_sales GROUP BY product), w AS (SELECT product, SUM(units) AS warehouse_units FROM warehouse_out GROUP BY product), r AS (SELECT CASE WHEN t.till_units = w.warehouse_units THEN 'match' ELSE 'check' END AS status FROM t FULL OUTER JOIN w ON t.product = w.product) SELECT status, COUNT(*) AS products FROM r GROUP BY status

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed