Table Joins (3) — Stacking WHERE / ORDER BY / CASE on a JOIN

The data we'll use — department, employee, and sales

In the previous articles you saw the different flavors of join — INNER JOIN, OUTER JOIN, and self-join.

In this article you'll stack WHERE filters, ORDER BY, and CASE band labels on top of a JOIN and build the shape of an aggregation report you'll actually meet at work.

The data is department (6 departments) and employee (30 staff), plus a new sales table (50 rows) of sales line items.

sales.emp_id is a foreign key that points to employee.emp_id.

We'll join the three tables to build a report that prints department and rep name next to each sale.

Before the exercises, let's confirm the column definitions and sample rows of the three tables — department, employee, and sales.

① Run PRAGMA table_info(table_name); on each table to inspect its columns.

② Run SELECT * FROM table_name LIMIT 5; on each table to preview the first 5 rows. Notice how sales.emp_id points to a row in employee.

Joining three tables — chain JOINs together

To join three or more tables, you just chain `JOIN ... ON ...` clauses one after another.

Write FROM sales s JOIN employee e ON s.emp_id = e.emp_id JOIN department d ON e.dept_id = d.dept_id — first link sales to employee on emp_id, then link that result to department on dept_id.

The most natural way to read the order is: "start from the line items (sales), then attach the employee and department info onto them."

Everything is INNER JOIN, so only rows that match across all three tables survive.

In this dataset all 12 employees with sales belong to some department, so the 3-table INNER JOIN returns all 50 rows of sales.

-- Attach rep name and dept_name to each sale (3-table INNER JOIN)
SELECT s.sale_id, e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
ORDER BY s.sale_id;

Imagine the requirement: "I want a report of sales line items with the responsible employee's name and their department name attached." (The explanation appears once your query runs correctly.)

① INNER JOIN sales (aliased s) with employee (alias e) on emp_id, then INNER JOIN that with department (alias d) on dept_id.

② Select the four columns s.sale_id, e.name, d.dept_name, and s.amount.

③ Order the result by s.sale_id ascending.

Filter with WHERE, sort with ORDER BY

Once the JOIN is in place, use WHERE to keep only the rows you need and ORDER BY to arrange them readably.

WHERE conditions can reference columns from any joined table.

WHERE d.location = 'Tokyo' keeps only sales from departments in Tokyo, and WHERE s.amount >= 400000 keeps only high-value sales.

SQL clauses are written in the order FROM → JOIN → ON → WHERE → ORDER BY.

WHERE filters rows after the join completes, and ORDER BY sorts last.

To sort by multiple columns, comma-separate them: ORDER BY col1 DESC, col2. Rows that tie on col1 are then ordered by col2 (a secondary key to stabilize the order).

-- Only sales from Osaka-based departments, highest amount first
SELECT e.name, d.dept_name, s.amount, s.sale_date
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.location = 'Osaka'
ORDER BY s.amount DESC, s.sale_id;

Imagine the requirement: "I want a report of sales in Tokyo-based departments worth 400,000 or more, sorted by amount descending."

① INNER JOIN sales (s), employee (e), and department (d) on emp_id and dept_id.

② Filter to rows where d.location is Tokyo and s.amount is at least 400000.

③ Select the three columns e.name, d.dept_name, and s.amount, sorted by s.amount descending. For rows tied on amount, use s.sale_id ascending as a secondary key to stabilize the order.

Add band labels with CASE — finish with a classified report

On top of JOIN, WHERE, and ORDER BY, a CASE expression can add a column with band labels for the amount, turning the result into a report a human can scan at a glance.

CASE evaluates conditions top-down and returns the value of the first branch that turns out true. You can put it directly inside the SELECT column list (searched form: CASE WHEN condition THEN value ... ELSE default END).

For example, split each sale into three bands by amount: High when amount >= 400000, Mid when amount >= 200000, and Low otherwise — exposing the band in a column aliased band.

Keep the labels as fixed ASCII strings ('High' / 'Mid' / 'Low') so the result stays portable across environments.

With 3-table JOIN, WHERE, ORDER BY, and CASE combined into one query, you end up with almost exactly the shape of the aggregation reports that get built every day in real teams.

-- Example: split amount into 2 bands (Large >= 300000)
SELECT e.name, d.dept_name, s.amount,
  CASE
    WHEN s.amount >= 300000 THEN 'Large'
    ELSE 'Small'
  END AS size
FROM sales s
JOIN employee e
  ON s.emp_id = e.emp_id
JOIN department d
  ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales'
ORDER BY s.sale_id;

Imagine the requirement: "I want a sales report with rep name, department name, and a band label, sorted by amount descending."

① INNER JOIN sales (s), employee (e), and department (d) across the three tables.

② Add a column aliased band using a searched-form CASE that returns 'High' when s.amount is at least 400000, 'Mid' when at least 200000, and 'Low' otherwise.

③ Select the four columns e.name, d.dept_name, s.amount, and band, sorted by s.amount descending with s.sale_id ascending as a tiebreaker.

Imagine the requirement: "I want a summary that totals each rep's sales and tiers them as Gold / Silver / Bronze by total."

① INNER JOIN sales (s), employee (e), and department (d).

② Group by e.name and d.dept_name, and aggregate SUM(s.amount) aliased total.

③ Add a column aliased tier using a searched-form CASE that returns 'Gold' when SUM(s.amount) is at least 1500000, 'Silver' when at least 1000000, and 'Bronze' otherwise.

④ Select the four columns e.name, d.dept_name, total, and tier, sorted by total descending with e.name ascending as a tiebreaker.

Employees with no sales disappear under INNER JOIN

Every report in this article uses INNER JOIN, so the 18 employees without any sales never show up in the result.

If you need a report that includes every employee — with zeros for the ones who had no sales — start with employee as the spine and LEFT JOIN sales onto it, then wrap the SUM in COALESCE(SUM(s.amount), 0) to convert NULL totals to 0.

Whenever a report's numbers don't add up, the first thing to suspect is "the wrong kind of join is silently dropping rows that should be there."