JOIN — Learn Practical Join Patterns in Practice

QUESTION 1

SELF JOIN — Join One Table in Two Roles to Represent a Hierarchical Relationship

SELF JOINSelf-joinOrganization hierarchyAliases required

Background ▼

A SELF JOIN references the same table through two different aliases and joins rows to one another.
A common example is an organization hierarchy where employees and their managers are stored in the same employees table and manager_id references an emp_id in that table.

FROM   employees AS e   -- reference the subordinate side
LEFT JOIN employees AS m  -- reference the manager side
  ON e.manager_id = m.emp_id;

Aliases are essential: In a SELF JOIN, you must use different aliases, such as AS e (subordinate) and AS m (manager). Without aliases, the database cannot tell which instance of the table a column refers to.

Problem ▼

The employees table stores each employee's manager_id, which is the emp_id of their direct manager.

SELF JOIN this table and retrieve a list of each employee's name (employee) and their direct manager's name (manager). Include employees with no manager (the company president) in the output.

Tables used

▸ employees

emp_id	name	manager_id
1	Tanaka	NULL
2	Sato	1
3	Yamada	1
4	Suzuki	2

Expected Output ▼

employee	manager
Tanaka	NULL
Sato	Tanaka
Yamada	Tanaka
Suzuki	Sato

Model Answer ▼

SELECT
  e.name  AS employee,  -- subordinate-side name
  m.name  AS manager    -- manager-side name (NULL when manager_id is NULL)

FROM employees AS e          -- read the table from the subordinate's perspective
LEFT JOIN employees AS m    -- reuse the same table as the manager
  ON e.manager_id = m.emp_id  -- join when the subordinate's manager_id matches the manager's emp_id
ORDER BY e.emp_id;

/*
  Execution order:
  1. FROM employees AS e       → read 4 employee rows as the subordinate side
  2. LEFT JOIN employees AS m  → read the same table again as the manager side,
                                  matching on e.manager_id = m.emp_id
                                  Tanaka (manager_id=NULL) has no matching manager → NULL padding
  3. SELECT e.name, m.name     → project the subordinate and manager names
  4. ORDER BY e.emp_id         → sort by emp_id in ascending order
*/

Explanation (table transitions & key points) ▼

SELECT
  e.name  AS employee,
  m.name  AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM employees AS e (read as subordinate)Read the employees table (4 rows) as the subordinate side. Each row's manager_id becomes the matching key for the next SELF JOIN.

1 / 3

emp_id	name	manager_id
1	Tanaka	NULL
2	Sato	1
3	Yamada	1
4	Suzuki	2

All 4 rows read (e = subordinate side)

LEARNING POINTS

JOIN PATTERN

SELF JOIN

Reference one table in two roles at the same time
e = subordinate view m = manager view

Why use LEFT JOIN: INNER JOIN would remove top-level employees with no manager (manager_id IS NULL) from the result. To output every employee while showing a manager name when one exists, LEFT JOIN is essential.

Extend to recursive hierarchies: This question retrieves only the manager one level above each employee. PostgreSQL and MySQL 8.0+ can use WITH RECURSIVE (a recursive CTE) to retrieve an organization tree across any number of levels in one query. Understanding SELF JOIN is essential as an introduction to recursive CTEs.

ANTI-PATTERNS

Omitting aliases: Writing FROM employees JOIN employees ON ... makes it ambiguous which employees table a column refers to, so the database cannot execute it correctly. In a SELF JOIN, aliases such as AS e and AS m are required syntax.

Splitting the table into separate tables: Physically managing separate “manager” and “subordinate” tables is redundant and makes data integrity difficult to maintain. A core RDB principle is to normalize the data into one table and use SELF JOIN.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

Anti-JOIN — Find Users Who Have Never Made a Purchase with LEFT JOIN + IS NULL

LEFT JOINIS NULLAnti-JOINSet difference

Background ▼

The pattern for retrieving “records that exist in table A but do not exist in table B” is called an anti-JOIN.
It uses two stages: keep every row with LEFT JOIN, then use WHERE to retain only rows where the right-table side is NULL.

FROM   users AS u
LEFT JOIN purchases AS p ON u.user_id = p.user_id
WHERE  p.user_id IS NULL;   -- keep only rows with no match in the right table

Choosing between this and NOT IN: WHERE u.user_id NOT IN (SELECT user_id FROM purchases) can return the same result, but NOT IN risks returning no rows if the right-side list contains NULL. The anti-JOIN pattern is safer and faster, so it is recommended in practical work.

Problem ▼

There are a user list (users) and a purchase-history table (purchases).

Retrieve only the names of users who have never made a purchase.

Tables used

▸ users

user_id	name
1	Tanaka
2	Sato
3	Yamada

▸ purchases

purchase_id	user_id	item
1	1	Item A
2	1	Item B
3	2	Item C

Expected Output ▼

name
Yamada

Model Answer ▼

SELECT
  u.name

FROM users AS u
LEFT JOIN purchases AS p
  ON u.user_id = p.user_id
WHERE p.purchase_id IS NULL;  -- a NULL right-side row after LEFT JOIN = a user with no purchase history

/*
  Execution order:
  1. FROM users AS u            → read the 3 user rows
  2. LEFT JOIN purchases AS p   → match by user_id. Tanaka and Sato match; Yamada does not
                                  every p-side column for Yamada is padded with NULL
  3. WHERE p.purchase_id IS NULL→ keep only the NULL-padded row (the user with no purchases)
                                  Tanaka and Sato are skipped because p.purchase_id has a real value
  4. SELECT u.name              → project the name from the remaining 1 row (Yamada)
*/

Explanation (table transitions & key points) ▼

SELECT
  u.name
FROM users AS u
LEFT JOIN purchases AS p
  ON u.user_id = p.user_id
WHERE p.purchase_id IS NULL;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM users AS uRead the users table (3 rows) as the left table. Keeping all of these rows while joining is the defining behavior of LEFT JOIN.

1 / 4

user_id	name
1	Tanaka
2	Sato
3	Yamada

All 3 rows read

LEARNING POINTS

JOIN PATTERN

Anti-JOIN (set difference)

Rows found only in u = users with no purchase history
u = users p = purchases

How to choose the column for the IS NULL filter: WHERE p.user_id IS NULL also works, but using a NOT NULL primary-key column (p.purchase_id) is the most reliable choice. If you use an ordinary column that may contain NULL, you cannot distinguish a user with purchase history from a row where that column's value is genuinely NULL.

Typical practical use cases: This pattern appears every day when extracting differences for marketing and operations, such as “members registered for a newsletter but with no orders,” “users without assigned permissions,” and “purchasers who have not written a review.”

ANTI-PATTERNS

NULL values inside NOT IN cause unexpected results: If the subquery result in WHERE u.user_id NOT IN (SELECT user_id FROM purchases) contains even one NULL, NOT IN always returns UNKNOWN and unexpectedly returns no rows. The anti-JOIN pattern avoids this problem and is safer.

FIELD NOTE

PostgreSQL 9.5+ can also retrieve a set difference with the EXCEPT operator (SELECT user_id FROM users EXCEPT SELECT user_id FROM purchases). EXCEPT may be more readable for a simple set difference, but the anti-JOIN pattern is more flexible and general-purpose when you need additional columns or filters.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

Aggregate JOIN — Calculate Department Averages in a Subquery First, Then Compare Individual Salaries

INNER JOINGROUP BYSubqueryCompare with aggregate

Background ▼

To determine whether each employee's salary is above or below the average for their department, first aggregate the data and then join it to the individual records.
Use a subquery (or the CTE described later) to perform the GROUP BY aggregation first, then JOIN that result table so you can attach the aggregate value while retaining each individual row.

FROM employees AS e
INNER JOIN (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM   employees
  GROUP BY dept_id
) AS da ON e.dept_id = da.dept_id;

Why can’t AVG be written in WHERE: WHERE salary > AVG(salary) is syntactically invalid. WHERE evaluates individual rows before aggregation, so aggregate functions cannot be used there. “Aggregate first → JOIN the result table” is the correct design.

Problem ▼

The employees table stores salary (salary) and department ID (dept_id).

Retrieve a list of each employee's name, salary, and the average salary for their department (avg_salary).

Tables used

▸ employees

emp_id	name	dept_id	salary
1	Tanaka	10	500
2	Sato	10	700
3	Yamada	20	600
4	Suzuki	20	400

Expected Output ▼

name	salary	avg_salary
Tanaka	500	600
Sato	700	600
Yamada	600	500
Suzuki	400	500

Model Answer ▼

SELECT
  e.name,
  e.salary,
  da.avg_salary  -- department average salary calculated in the subquery

FROM employees AS e
INNER JOIN (
  -- first aggregate the average salary by department and create a virtual table
  SELECT
    dept_id,
    AVG(salary) AS avg_salary
  FROM   employees
  GROUP BY dept_id
) AS da                    -- abbreviation of "dept_avg" (derived table)
  ON e.dept_id = da.dept_id;

/*
  Execution order:
  1. Inside the subquery: FROM employees                  → GROUP BY dept_id
  2. FROM employees AS e                     → read the employee table
  3. INNER JOIN da ON dept_id match             → attach the department average to each employee row
  4. SELECT e.name, e.salary, da.avg_salary  → project the required columns
  */

Explanation (table transitions & key points) ▼

SELECT
  e.name,
  e.salary,
  da.avg_salary
FROM employees AS e
INNER JOIN (
  SELECT
    dept_id,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS da
  ON e.dept_id = da.dept_id;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① Subquery GROUP BY ▼

SELECT dept_id, AVG(salary) AS avg_salary GROUP BY dept_idThe subquery inside the parentheses runs first. Group the employees table by dept_id and calculate the average salary for each department. This aggregate result becomes the derived table da.

1 / 4

dept_id	name	salary	→ avg_salary
10	Tanaka	500
10	Sato	700	600.00
20	Yamada	600
20	Suzuki	400	500.00

After GROUP BY: 2 groups → 2-row virtual table (da) generated

LEARNING POINTS

JOIN PATTERN

INNER JOIN to an aggregate subquery

Aggregate with GROUP BY first → attach the result to individual rows
e = individual data da = department aggregate

The design principle of “matching row grain”: employees (4 rows at individual grain) and the aggregate result da (2 rows at department grain) have different row counts. The JOIN still preserves the individual grain (4 rows) because the more granular side (e) is the base table. Attaching data with a different grain through JOIN is a core practical SQL technique.

Add ranking with a CASE expression: Compare avg_salary with salary and append CASE WHEN e.salary > da.avg_salary THEN 'Above average' ELSE 'Below average' END to add an evaluation label. This is one of the most common patterns in dashboard-oriented aggregate SQL.

ANTI-PATTERNS

Putting an aggregate function in WHERE: WHERE e.salary > AVG(e.salary) is invalid syntax. WHERE evaluates individual rows before aggregation, so aggregate functions such as AVG and SUM have not been calculated yet. If you need to compare with an aggregate, “create the aggregate table first and JOIN it” is the correct approach.

FIELD NOTE

This query can be rewritten with the CTE (WITH clause) covered in Q5 to eliminate subquery nesting and improve readability. The shape is WITH dept_avg AS (SELECT dept_id, AVG(salary) ... GROUP BY dept_id) SELECT ... FROM employees JOIN dept_avg .... The difference between a subquery and a CTE is mainly how it is written; their execution plans are generally similar, though this can vary by database.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

Chained LEFT JOIN Across Three Tables — Combine Orders, Users, and Items in an E-Commerce Query

LEFT JOIN ×2Chained joinsE-commerce designPreserve NULLs

Background ▼

Practical web applications frequently need to join three or more tables in one query.
When LEFT JOINs are chained, the result of all joins up to that point is always the “left table.” The key to preserving NULLs is that a missing match in a right-side table must not remove the existing rows.

FROM         orders      AS o
LEFT JOIN    users       AS u  ON o.user_id    = u.user_id
LEFT JOIN    order_items AS oi ON o.order_id   = oi.order_id;

JOIN order determines the result: When LEFT JOINs are chained, the table named in FROM is the most protected side. Even when a later table has no match, the original row always remains in the result.

Problem ▼

There are three tables: orders (orders), users (users), and order details (order_items).

Join all three tables and retrieve the order ID, user name, product name, and quantity. Include the order with no registered product (order_id=3) in the output.

Tables used

▸ orders

order_id	user_id
1	1
2	2
3	3

▸ users

user_id	name
1	Tanaka
2	Sato
3	Yamada

▸ order_items

item_id	order_id	product_name	qty
1	1	T-shirt	2
2	1	Jeans	1
3	2	Sneakers	1

Expected Output ▼

order_id	name	product_name	qty
1	Tanaka	T-shirt	2
1	Tanaka	Jeans	1
2	Sato	Sneakers	1
3	Yamada	NULL	NULL

Model Answer ▼

SELECT
  o.order_id,
  u.name,
  oi.product_name,
  oi.qty

FROM orders AS o
LEFT JOIN users AS u           -- ① attach user information while keeping every order
  ON o.user_id = u.user_id
LEFT JOIN order_items AS oi    -- ② attach details while keeping orders with no details as NULL-padded rows
  ON o.order_id = oi.order_id

ORDER BY o.order_id, oi.item_id;

/*
  Execution order:
  1. FROM orders AS o                 → the order table
  2. LEFT JOIN users AS u             → match by user_id
  3. LEFT JOIN order_items AS oi      → match by order_id
  4. SELECT ...                       → project the required 4 columns
  5. ORDER BY o.order_id, oi.item_id  → sort by order ID and item ID
  */

Explanation (table transitions & key points) ▼

SELECT
  o.order_id,
  u.name,
  oi.product_name,
  oi.qty
FROM orders AS o
LEFT JOIN users AS u
  ON o.user_id = u.user_id
LEFT JOIN order_items AS oi
  ON o.order_id = oi.order_id
ORDER BY o.order_id, oi.item_id;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM orders AS oRead the orders table (3 rows) as the starting point. These 3 rows are the starting left table for the chain of LEFT JOINs.

1 / 4

order_id	user_id
1	1
2	2
3	3

All 3 rows read

LEARNING POINTS

JOIN PATTERN

Chained LEFT JOIN across 3 tables

Chain left joins starting from orders
row count changes with one-to-many joins

How one-to-many JOINs increase the row count: order_id=1 has 2 rows in order_items, so it expands to 2 rows after the LEFT JOIN. This is correct behavior. Conversely, joining without considering the one-to-many relationship can inflate data, so be especially careful when combining the join with aggregation such as SUM or COUNT.

Choosing between this and INNER JOIN: If an order with no details can be excluded, you can use INNER JOIN for every join. In practice, however, an order may exist without details because it is still in a cart or payment is incomplete, so many designs safely retain every order with LEFT JOIN.

ANTI-PATTERNS

Aggregating after a one-to-many JOIN inflates data: If you add SUM(oi.qty) to this joined result, order_id=1 correctly becomes T-shirt (2) + Jeans (1) = 3, but any one-to-one aggregate column such as a user count is duplicated as the row count expands. When you “join, then aggregate,” always check the change in row grain.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

CTE + JOIN — Pre-Aggregate with WITH, Then Join User Information in a Modern SQL Pattern

WITH (CTE)LEFT JOINCOALESCEImprove readability

Background ▼

A CTE (Common Table Expression) defines a named virtual table at the beginning of a SQL statement in the form WITH name AS (subquery).
Instead of nesting complex subqueries, you can write a structure that reads from top to bottom, so code readability improves dramatically.

WITH cte_name AS (
  SELECT ...  -- pre-aggregation or shaping
)
SELECT ...
FROM   main_table
LEFT JOIN cte_name ON ...;

A recommended modern SQL style: CTEs are supported by all major RDBs and warehouses, including PostgreSQL, MySQL 8.0, BigQuery, and Snowflake. The main advantage is that the same result as the Q3 subquery JOIN can be written more readably.

Problem ▼

There are a users table (users) and an orders table (orders).

Use a WITH clause to aggregate each user's order count (order_count) and total amount (total_amount), then join the result with the user information and output it.
Also output the user with no orders (Yamada): show the order count as 0 and the total amount as NULL.

Tables used

▸ users

user_id	name
1	Tanaka
2	Sato
3	Yamada

▸ orders

order_id	user_id	amount
1	1	3000
2	1	5000
3	2	2000

Expected Output ▼

name	order_count	total_amount
Tanaka	2	8000
Sato	1	2000
Yamada	0	NULL

Model Answer ▼

-- ① CTE: define a per-user order aggregate table
WITH order_stats AS (
  SELECT
    user_id,
    COUNT(*)      AS order_count,   -- order count
    SUM(amount)    AS total_amount  -- total amount
  FROM   orders
  GROUP BY user_id
)

-- ② Main query: LEFT JOIN users with the CTE
SELECT
  u.name,
  COALESCE(os.order_count, 0) AS order_count,  -- convert NULL to 0
  os.total_amount                              -- users with no orders (Yamada) remain NULL

FROM users AS u
LEFT JOIN order_stats AS os
  ON u.user_id = os.user_id

ORDER BY u.user_id;

/*
  Execution order:
  1. Evaluate the CTE (order_stats)
  2. FROM users AS u              → the user table
  3. LEFT JOIN order_stats AS os  → match by user_id
  4. SELECT COALESCE(...)         → convert NULL order_count to 0 and project the columns
  5. ORDER BY u.user_id           → sort by user_id in ascending order
  */

Explanation (table transitions & key points) ▼

WITH order_stats AS (
  SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
)
SELECT
  u.name,
  COALESCE(os.order_count, 0) AS order_count,
  os.total_amount
FROM users AS u
LEFT JOIN order_stats AS os
  ON u.user_id = os.user_id
ORDER BY u.user_id;

LEGEND ▼

Grouping keys / aggregation targets

Group classification

① WITH (CTE definition) ▼

order_stats AS (SELECT user_id, COUNT(*), SUM(amount) FROM orders GROUP BY user_id)The query inside the CTE runs first. Group orders by user_id and aggregate the order count and total amount to create a virtual table named order_stats. Yamada (user_id=3) has no row in orders, so Yamada is not included in the CTE.

1 / 4

user_id	order_id	amount	→ count	→ sum
1	1	3000
1	2	5000	2	8000
2	3	2000	1	2000

CTE (order_stats) generated: 2 rows (user_id=3 is absent)

LEARNING POINTS

JOIN PATTERN

CTE (WITH) + LEFT JOIN

Define a pre-aggregation by name and create a structure that reads from the top down
WITH → FROM → JOIN → SELECT flow

The difference between a CTE and the Q3 subquery is readability: In Q3, the subquery was nested inside parentheses, but the same aggregation can be moved to the beginning of the SQL statement with a CTE. The processing flow—“what should be prepared first?” → “how should it be used?”—reads naturally from top to bottom, which is highly valuable for team code reviews and maintenance. The execution result is equivalent.

Convert NULL into a meaningful value with COALESCE: COALESCE(value, default) returns the second argument when the first argument is NULL. Leaving “zero orders = NULL” often causes application-side bugs, so converting it to 0 at the SQL stage is a practical best practice. Return total_amount as NULL and choose the handling according to the use case, such as displaying “-” in the application.

ANTI-PATTERNS

Defining too many CTEs: CTEs are convenient and you can define any number of them in one query, but excessive splitting—such as defining unused CTEs or turning every one-line subquery into a CTE—can make the query harder to read. Limit them to aggregates referenced in multiple places and processes that would otherwise become deeply nested.

Forgetting COALESCE despite using LEFT JOIN: If an aggregate value made NULL by LEFT JOIN is output as-is, the application may perform unintended operations such as “null + 1.” For counts, use COALESCE(count, 0); for amounts, intentionally decide whether to return NULL or handle it in the application.

FIELD NOTE

In data warehouses such as BigQuery and Snowflake, chaining multiple CTEs to write a data-transformation pipeline in one file is a mainstream style. Stacking named transformation steps from top to bottom, as in WITH step1 AS (...), step2 AS (...SELECT FROM step1...), step3 AS (...) SELECT ... FROM step3, is also standard in dbt (data build tool) and is an essential skill in analytics engineering.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.