Composing Queries — Combining JOIN, Subqueries, and UNION

Data used in this article — employee / department / sales

JOIN (connecting multiple tables), subqueries (a query inside a query), and UNION (stacking results vertically) each work on their own, but when you combine them with WHERE / ORDER BY / LIMIT you can write practical, real-world queries.

In this article you'll walk through, in order, how to combine these techniques and grow them into a single query.

The material is three employee tables — employee (30 employees), department (6 departments), and sales (50 sales line items).

You'll start with a simple JOIN, then build up through subqueries and UNION step by step.

Before diving into the exercises, take a moment to check the column definitions and a sample of the data for the three tables used in this article — employee / department / sales.

① Use PRAGMA table_info(...) to check the column definitions of the three tables.

② Use SELECT * FROM table_name LIMIT 5; to preview the first 5 rows of each table.

Nail down the order clauses are evaluated — JOIN, WHERE, ORDER BY, LIMIT

The order you write a query in (SELECT … FROM … WHERE … ORDER BY) is different from the order it's evaluated in.

The database first connects tables with FROM / JOIN, then narrows rows with WHERE, aggregates with GROUP BY / HAVING, builds columns with SELECT, and finally sorts with ORDER BY and limits the row count with LIMIT.

Once you have this order down, rules like "to filter on an aggregated result use HAVING, not WHERE" and "LIMIT takes effect after sorting" start to make sense.

-- JOIN + WHERE + ORDER BY: employees in Osaka departments, ordered by hire date
SELECT emp.name, dept.dept_name, emp.hired_on
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE dept.location = 'Osaka'
ORDER BY emp.hired_on;

Combine JOIN, WHERE, ORDER BY, and LIMIT in a single query. (Run it correctly and the explanation appears.)

① Inner join employee and department on dept_id and pull out the employee name, department name, and salary.

② Narrow down to only the employees whose department location (department.location) is Tokyo.

③ Sort by salary from highest to lowest and take only the top 5.

Build a reference value with a subquery and narrow the JOIN result

A subquery can be used to build the "reference value" you need to further narrow down a result you assembled with JOIN.

A criteria value that changes per employee, like "the average salary of my own department," is computed with a correlated subquery (a subquery that's recalculated for each individual employee) that references the outer row.

When you attach the department name with JOIN and narrow rows using a correlated subquery condition, several pieces of syntax mesh together in one query.

-- JOIN + correlated subquery: employees whose salary equals their own department's max (the top earner in the department)
SELECT emp.name, dept.dept_name, emp.salary
FROM employee emp
JOIN department dept ON dept.dept_id = emp.dept_id
WHERE emp.salary = (
  SELECT MAX(dept_member.salary)
  FROM employee dept_member
  WHERE dept_member.dept_id = emp.dept_id
);

This time you'll stack subqueries. List, with department name, the employees in each department who earn "more than their own department's average salary."

① Join employee and department and pull out the employee name, department name, and salary.

② Use a correlated subquery to compute the average salary of that employee's own department, and narrow down to only employees whose salary is greater than it.

③ Sort by dept_id ascending, and within the same department by salary from highest to lowest.

Combine two perspectives with UNION and finish with ORDER BY

UNION stacks the results of two SELECTs vertically.

The upper and lower SELECTs must be union-compatible (the same number of columns, with the types of the corresponding columns being compatible).

UNION collapses fully identical rows into one (removing duplicates), while UNION ALL keeps duplicates as-is.

Sorting and row-count limits apply to the combined result as a whole, so write ORDER BY / LIMIT only once, at the very end.

-- UNION ALL: stack two perspectives vertically with a label column (keeps duplicates)
SELECT name, 'Kyoto' AS via FROM employee WHERE city = 'Kyoto'
UNION ALL
SELECT emp.name, 'HighSales' AS via
FROM employee emp
JOIN sales sale ON sale.emp_id = emp.emp_id
GROUP BY emp.emp_id
HAVING SUM(sale.amount) >= 1500000
ORDER BY name;

Finally, combine two perspectives into a single roster with UNION.

① Write a SELECT that pulls out the names of "employees living in Kyoto."

② Write a SELECT that finds "employees whose total sales are 1.5 million yen or more" using a join of employee and sales plus aggregation (GROUP BY / HAVING).

③ Connect the two with UNION to collapse duplicates into a single row, and sort by name ascending.