JOIN — Learn Complex Join Patterns in Practice

QUESTION 6

FULL OUTER JOIN — Reconcile Two Datasets Completely While Preserving Missing Rows on Both Sides

FULL OUTER JOINCOALESCEData reconciliationMissing on either side

Background ▼

LEFT JOIN keeps every row from the left table, and RIGHT JOIN keeps every row from the right table. FULL OUTER JOIN keeps every row from both tables, so no row is missed. A row that exists on only one side is padded with NULL on the other side.

-- Comparing join types (left: A={1,2,3}, right: B={1,3,4})
LEFT  JOIN → 1, 2, 3       (2 is absent from B, so the B side is NULL)
RIGHT JOIN → 1, 3, 4       (4 is absent from A, so the A side is NULL)
FULL  JOIN → 1, 2, 3, 4    (B is NULL for 2; A is NULL for 4)

Use COALESCE to fill NULLs from both sides: FULL JOIN can produce NULLs on either side. For columns such as IDs or names that may exist in either table, write both sides as COALESCE(lm.col, tm.col) to fill the NULL.

Not supported by MySQL: MySQL does not support FULL OUTER JOIN. Use LEFT JOIN … UNION ALL … RIGHT JOIN WHERE A.id IS NULL as an alternative. PostgreSQL, BigQuery, and SQL Server support it.

Problem ▼

An e-commerce site has product-sales tables for last month (last_month) and this month (this_month). Some products sold only last month, some only this month, and some in both months.

Cover every product and place last month's sales (last_amount) next to this month's sales (this_amount). A product missing from one month should have NULL for that month.

Tables used

▸ last_month

product_id	product_name	amount
1	PC	500
2	Mouse	120
3	Keyboard	280

▸ this_month

product_id	product_name	amount
1	PC	630
3	Keyboard	210
4	Monitor	450

Expected Output ▼

product_id	product_name	last_amount	this_amount
1	PC	500	630
2	Mouse	120	NULL
3	Keyboard	280	210
4	Monitor	NULL	450

Model Answer ▼

SELECT
  COALESCE(lm.product_id,   tm.product_id)   AS product_id,
  COALESCE(lm.product_name, tm.product_name) AS product_name,
  lm.amount AS last_amount,
  tm.amount AS this_amount

FROM last_month AS lm
FULL OUTER JOIN this_month AS tm    -- keep rows that exist in only one of the tables
  ON lm.product_id = tm.product_id
ORDER BY
  product_id;

/*
  Execution order:
  1. FROM last_month AS lm       → left table
  2. FULL OUTER JOIN this_month  → match by product_id
  3. SELECT COALESCE(...)        → fill columns that may be NULL on either lm or tm side
  4. ORDER BY product_id         → output in ascending order
  */

Explanation (table transitions & key points) ▼

SELECT
  COALESCE(lm.product_id, tm.product_id) AS product_id,
  COALESCE(lm.product_name, tm.product_name) AS product_name,
  lm.amount AS last_amount,
  tm.amount AS this_amount
FROM last_month AS lm
FULL OUTER JOIN this_month AS tm
  ON lm.product_id = tm.product_id
ORDER BY
  product_id;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM last_month AS lmRead the last-month sales table (3 rows) as the base. It contains product_id 1, 2, and 3.

1 / 3

product_id	product_name	amount
1	'PC'	500
2	'Mouse'	120
3	'Keyboard'	280

All 3 rows read

LEARNING POINTS

JOIN PATTERN

FULL OUTER JOIN (complete outer join · union of both sides)

Reconcile completely without missing rows from either side
lm = last_month tm = this_month

Use it for data reconciliation and discrepancy checks: FULL OUTER JOIN is most useful when comparing two data sources, such as a product master in a database and an external CSV product list, to find differences that exist on only one side. It lets you review matched rows, left-only rows, and right-only rows at once, making it essential for data-integrity checks.

Use COALESCE for columns whose side is unknown: In a normal LEFT JOIN, NULL comes only from the right side, but FULL JOIN can produce NULLs on either side. For master-data columns such as product_id and product_name, remember to write both sides as COALESCE(lm.col, tm.col).

ANTI-PATTERNS

Accidentally remove NULL rows with WHERE: A condition such as WHERE lm.amount > 0 removes every row where lm is NULL, including products sold only this month. If you need to filter one side after a FULL JOIN, explicitly allow NULL with WHERE lm.amount > 0 OR lm.amount IS NULL.

Use FULL OUTER JOIN directly in MySQL: MySQL does not support FULL OUTER JOIN. The alternative combines SELECT … FROM A LEFT JOIN B … UNION ALL SELECT … FROM A RIGHT JOIN B … WHERE A.id IS NULL, but readability drops significantly. Always verify the database environment.

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 7

Anti-JOIN — Use LEFT JOIN + WHERE IS NULL to Filter for Non-Existence

LEFT JOINAnti-JOINSet differenceNOT EXISTS

Background ▼

The operation of retrieving “rows that exist in table A but not in table B” is called an anti-JOIN (set difference). It is common in practical work for detecting churned users, finding unprocessed tickets, and identifying data that is missing from a master table.

There are two main ways to implement it.

-- ① LEFT JOIN + WHERE IS NULL (the method used in this question)
SELECT a.* FROM A LEFT JOIN B ON a.id = b.id
WHERE b.id IS NULL;

-- ② NOT EXISTS (more explicit intent)
SELECT a.* FROM A
WHERE NOT EXISTS (SELECT 1 FROM B WHERE b.id = a.id);

Do not use NOT IN: If the subquery result for WHERE a.id NOT IN (SELECT id FROM B) contains even one NULL, every row becomes UNKNOWN and the result is empty. Use LEFT JOIN or NOT EXISTS for an anti-JOIN.

Problem ▼

There are tables of customers who purchased last month (last_buyers) and customers who purchased this month (this_buyers).

Retrieve the user_id and user_name of customers who purchased last month but not this month (churned customers).

Tables used

▸ last_buyers

user_id	user_name
1	Tanaka
2	Sato
3	Suzuki
4	Takahashi

▸ this_buyers

user_id
1
3

Expected Output ▼

user_id	user_name
2	Sato
4	Takahashi

Model Answer ▼

SELECT
  l.user_id,
  l.user_name

FROM last_buyers AS l
LEFT JOIN this_buyers AS t  -- keep every last-month customer while matching this-month data
  ON l.user_id = t.user_id
WHERE t.user_id IS NULL;    -- a NULL right side (this_buyers) = a customer absent this month

/*
  Execution order:
  1. FROM last_buyers AS l       → all last-month customers
  2. LEFT JOIN this_buyers AS t  → match by user_id
  3. WHERE t.user_id IS NULL     → keep only NULL-padded rows (absent this month)
  4. SELECT l.user_id, ...       → project and output churned-customer data
  */

Explanation (table transitions & key points) ▼

SELECT
  l.user_id,
  l.user_name
FROM last_buyers AS l
LEFT JOIN this_buyers AS t
  ON l.user_id = t.user_id
WHERE t.user_id IS NULL;

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM last_buyers AS lRead all last-month customers (4 rows) as the base table.

1 / 4

user_id	user_name
1	Tanaka
2	Sato
3	Suzuki
4	Takahashi

All 4 rows read

LEARNING POINTS

JOIN PATTERN

ANTI JOIN (set difference · detecting absence)

Retrieve only rows from the left table that do not exist in the right table
l = last_buyers t = this_buyers

A JOIN is needed to express “does not exist”: SQL's WHERE clause can evaluate only values on the current row. A condition such as “does not exist in another table” cannot be determined from one table alone, so use a LEFT JOIN or a correlated subquery to reference the other table.

Choosing between LEFT JOIN and NOT EXISTS: The database engine often optimizes them to similar performance, but NOT EXISTS is clearer in intent. “This user's order does not exist” is more intuitive as WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = u.user_id).

ANTI-PATTERNS

NOT IN + a subquery is vulnerable to NULL: If even one NULL appears in the subquery result, NOT IN returns UNKNOWN for every row and the result has 0 rows. This silent bug produces no error or warning and is very difficult to find. Always use LEFT JOIN or NOT EXISTS for an anti-JOIN.

SELECT l.user_id, l.user_name
FROM last_buyers AS l
WHERE l.user_id NOT IN (
  SELECT user_id
  FROM this_buyers
);

LEGEND ▼

Rows read / loaded

① Subquery result (with NULL) ▼

SELECT user_id FROM this_buyers ← a case where one NULL is includedConsider a case where a NULL has entered this_buyers because of a data-quality problem. NOT IN causes a critical bug in this situation.

1 / 2

user_id
1
3
NULL

3 rows including NULL (a realistic possibility)

Practical column: Anti-JOIN in production

The most common anti-JOIN use case in practice is extracting unprocessed or unhandled data: tickets with an inquiry but no reply record, or orders that exist but are missing from the shipping table. Master LEFT JOIN + WHERE IS NULL as a reliable pattern for quickly detecting these gaps.

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 8

Many-to-Many (M:N) JOIN — Traverse a Junction Table in Two Steps to Search by Tag

INNER JOINMany-to-manyJunction tableTag system

Background ▼

A many-to-many (M:N) relationship—where one article can have multiple tags and one tag can belong to multiple articles—is modeled with a junction table containing two foreign keys. This is one of the most common structures in web-application database design.

-- A typical many-to-many database structure
articles      (article_id, title)       ← entity A
tags          (tag_id, tag_name)        ← entity B
article_tags  (article_id, tag_id)     ← junction table (bridge)

-- Retrieval pattern: traverse articles → article_tags → tags in two steps
FROM articles AS a
INNER JOIN article_tags AS at ON a.article_id = at.article_id
INNER JOIN tags         AS t  ON at.tag_id    = t.tag_id

When DISTINCT is needed: If an article has multiple target tags, the same article expands into multiple rows. When you need only an article list, the standard approach is to remove duplicates with SELECT DISTINCT.

Problem ▼

There are an article table (articles), a tag table (tags), and a junction table (article_tags) that links them.

Retrieve the article_id and title of articles tagged with the tag name 'React'.

Tables used

▸ articles

article_id	title
1	Build an SPA with React
2	Complete Guide to SQL JOIN
3	Compare Vue and React

▸ tags

tag_id	tag_name
1	React
2	SQL
3	Vue

▸ article_tags (junction table)

article_id	tag_id
1	1
2	2
3	1
3	3

Expected Output ▼

article_id	title
1	Build an SPA with React
3	Compare Vue and React

Model Answer ▼

SELECT DISTINCT
  a.article_id,
  a.title

FROM articles AS a
INNER JOIN article_tags AS at      -- ① connect articles to the junction table by article_id
  ON a.article_id = at.article_id
INNER JOIN tags AS t               -- ② connect the junction table to tags by tag_id (second step)
  ON at.tag_id = t.tag_id
WHERE t.tag_name = 'React';        -- ③ filter by tag name

/*
  Execution order:
  1. FROM articles AS a             → article table
  2. INNER JOIN article_tags AS at  → match by article_id
  3. INNER JOIN tags AS t           → match by tag_id and add the tag name
  4. WHERE t.tag_name = 'React'     → keep only rows tagged React
  5. SELECT DISTINCT ...            → remove duplicates and output article_id, title
  */

Explanation (table transitions & key points) ▼

SELECT DISTINCT
  a.article_id,
  a.title
FROM articles AS a
INNER JOIN article_tags AS at
  ON a.article_id = at.article_id
INNER JOIN tags AS t
  ON at.tag_id = t.tag_id
WHERE t.tag_name = 'React';

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM articles AS aRead the article table (3 rows) as the base.

1 / 5

article_id	title
1	'Build an SPA with React'
2	'Complete Guide to SQL JOIN'
3	'Compare Vue and React'

All 3 rows read

LEARNING POINTS

JOIN PATTERN

M:N many-to-many JOIN (through a junction table)

Traverse articles ← article_tags → tags in two steps
The junction table serves as the bridge

Why a junction table is needed: Putting tag_id directly on an article would prevent an article from having multiple tags. A junction table enables a flexible design where any number of tags can be attached to one article. M:N patterns appear everywhere in practice: article × tag, user × role, and product × category.

Keep row expansion in mind: A JOIN with a junction table increases the rows for an article with multiple tags by the number of tags. Always track this expansion, and use SELECT DISTINCT or GROUP BY when needed to aggregate it for an accurate result.

ANTI-PATTERNS

Forgetting DISTINCT: An article with multiple tags, such as article 3, expands to two or more rows through the junction-table JOIN. If you want only article_id, title but forget DISTINCT, the same article is output multiple times. Always remember that a JOIN through a junction table increases the row count.

Escaping to an array column: Some databases store tags in a TEXT[] array, but later tasks such as finding every article with the React tag or adding, removing, and renaming tags become difficult. A normalized junction table directly supports scalability.

Practical column: M:N design and junction tables

M:N JOINs exist in almost every modern web application: products × categories in e-commerce, users × follows in social networks (a self-referencing junction table), and users × roles in access control. An ORM resolves these relationships automatically, but behind the scenes it always issues this two-step INNER JOIN. Understanding it at the SQL level matters when performance problems arise.

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 9

CTE + JOIN — Build a Virtual Table Step by Step with WITH to Implement Cohort Analysis

CTE / WITHLEFT JOINCohort analysisMulti-stage aggregation

Background ▼

Packing a complex aggregation into one SELECT statement causes nested subqueries to pile up, sharply reducing readability and maintainability. A WITH clause (CTE: Common Table Expression) lets you break the aggregation into named virtual tables step by step, then JOIN them in the main query.

-- Basic CTE syntax
WITH cte_name AS (
  SELECT ...          -- write the aggregation query here
  FROM   source_table
  GROUP BY ...
)
SELECT *
FROM   main_table
LEFT JOIN cte_name ON ...;  -- a CTE can be joined as a table

Put date calculations in the ON clause: A LEFT JOIN ON clause can contain calculated expressions, not only equality conditions, such as fo.first_order_date <= u.register_date + INTERVAL '30 days'. Putting the condition in ON rather than WHERE applies it without removing rows from the left side of the LEFT JOIN.

Problem ▼

There are tables for user registrations (users) and order history (orders). A user may place multiple orders.

For each registration month (cohort month), count users who made their first purchase within 30 days of registration (converted_users) and all registered users (total_users).

Tables used

▸ users

user_id	register_date
1	2024-04-01
2	2024-04-20
3	2024-05-05
4	2024-05-10

▸ orders

order_id	user_id	order_date
101	1	2024-04-10
102	1	2024-06-01
103	2	2024-07-01
104	3	2024-05-15

Expected Output ▼

cohort_month	total_users	converted_users
2024-04	2	1
2024-05	2	1

Model Answer ▼

-- ① Aggregate only each user's first order date in a CTE
WITH first_orders AS (
  SELECT
    user_id,
    MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY user_id
)
SELECT
  DATE_TRUNC('month', u.register_date) AS cohort_month,
  COUNT(u.user_id)  AS total_users,
  COUNT(fo.user_id) AS converted_users                   -- a non-NULL fo.user_id = a user who purchased within 30 days

FROM users AS u
LEFT JOIN first_orders AS fo                                       -- ② join the CTE and specify the “within 30 days” condition in ON
  ON  u.user_id           = fo.user_id
  AND fo.first_order_date <= u.register_date + INTERVAL '30 days'
GROUP BY
  cohort_month
ORDER BY
  cohort_month;

/*
  Execution order:
  1. [CTE] FROM orders             → order table
  2. [CTE] GROUP BY user_id        → calculate the first order date with MIN(order_date)
  3. FROM users AS u               → user table
  4. LEFT JOIN first_orders AS fo  → match by user_id and the within-30-days condition
  5. GROUP BY cohort_month         → group and aggregate by registration month
  */

Explanation (table transitions & key points) ▼

WITH first_orders AS (
  SELECT
    user_id,
    MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY user_id
)
SELECT
  DATE_TRUNC('month', u.register_date) AS cohort_month,
  COUNT(u.user_id) AS total_users,
  COUNT(fo.user_id) AS converted_users
FROM users AS u
LEFT JOIN first_orders AS fo
  ON u.user_id = fo.user_id
  AND fo.first_order_date <= u.register_date + INTERVAL '30 days'
GROUP BY
  cohort_month
ORDER BY
  cohort_month;

LEGEND ▼

Rows read / loaded

① CTE FROM ▼

FROM orders ← processing inside CTE (first_orders)First read the orders table (4 rows) inside the CTE. User 1 has two orders, but MIN(order_date) selects only the first one.

1 / 6

order_id	user_id	order_date
101	1	'2024-04-10'
102	1	'2024-06-01'
103	2	'2024-07-01'
104	3	'2024-05-15'

All 4 orders rows read

LEARNING POINTS

PATTERN

CTE + JOIN (stepwise aggregation join)

Build the virtual table first with WITH, then JOIN it in the main query
CTE → LEFT JOIN + condition → GROUP BY → SELECT

The essence of a CTE is improved readability: A CTE is not magic that dramatically improves performance; it is a tool for organizing complex nested subqueries by naming each step. The same logic can be written with subqueries, but a CTE makes the query readable from top to bottom and makes review and debugging much easier.

Separate ON-clause and WHERE-clause conditions: If the “within 30 days” condition were written in WHERE, rows for users 2 and 4 preserved by the LEFT JOIN would be removed, changing the total_users count. To narrow match conditions without removing rows from the left side of a LEFT JOIN, put the condition in ON.

Practical column: Implementing cohort analysis

Cohort analysis is a core SaaS and e-commerce metric that tracks how a group of users who registered around the same time behaves afterward. In addition to this “first-purchase-within-30-days rate,” the same CTE + JOIN structure can implement retention 90 days after registration and cumulative sales by registration month. Multiple CTEs can be chained as WITH a AS (...), b AS (...) SELECT … FROM a JOIN b, supporting even more multi-stage analysis.

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 10

Relational-Division-Style JOIN — Join the Same Table Multiple Times to Find Rows That Satisfy A AND B

INNER JOINSelf-joinRelational divisionMultiple-condition AND

Background ▼

The problem “find users who have purchased both product A and product B” cannot be solved with a simple WHERE … IN. Each row represents only one purchase, so asking one row to be product A and product B at the same time is contradictory.

There are two solutions.

-- ① SELF JOIN the same table twice to place p1=product A and p2=product B side by side
FROM purchases AS p1
INNER JOIN purchases AS p2
  ON  p1.user_id = p2.user_id
WHERE p1.product_code = 'A' AND p2.product_code = 'B'

-- ② Aggregate first and filter with HAVING (alternative; useful with many products)
SELECT user_id FROM purchases
WHERE  product_code IN ('A', 'B')
GROUP BY user_id
HAVING COUNT(DISTINCT product_code) = 2

Relational division: The operation of finding groups that satisfy every specified requirement corresponds to “division” in relational algebra. If the number of products grows to three or more, add JOINs or change the condition to something like HAVING COUNT = 3.

Problem ▼

There is a purchase-history table (purchases). Each row records one purchase of a specific product by a user.

Retrieve the user_id of users who have purchased both product code 'A' and product code 'B'.

Tables used

▸ purchases

user_id	product_code
1	A
1	B
1	C
2	A
3	B
4	A
4	B

Expected Output ▼

user_id
1
4

Model Answer ▼

SELECT DISTINCT
  p1.user_id

FROM purchases AS p1
INNER JOIN purchases AS p2    -- self-join the same table as p2 (SELF JOIN)
  ON p1.user_id = p2.user_id
WHERE p1.product_code = 'A'   -- keep only pairs where p1 is product A and p2 for the same user is product B
  AND p2.product_code = 'B';

/*
  Execution order:
  1. FROM purchases AS p1         → all purchase history
  2. INNER JOIN purchases AS p2   → self-join the same table as p2
  3. WHERE p1.product_code = 'A'  → keep rows where the p1 side is product A
  4. SELECT DISTINCT p1.user_id   → remove duplicates and output user_id
  */

Explanation (table transitions & key points) ▼

SELECT DISTINCT
  p1.user_id
FROM purchases AS p1
INNER JOIN purchases AS p2
  ON p1.user_id = p2.user_id
WHERE p1.product_code = 'A'
  AND p2.product_code = 'B';

LEGEND ▼

Rows read / loaded

① FROM ▼

FROM purchases AS p1Read all purchase history (7 rows) as p1. Each row represents who bought which product.

1 / 4

user_id	product_code
1	'A'
1	'B'
1	'C'
2	'A'
3	'B'
4	'A'
4	'B'

All 7 rows read

LEARNING POINTS

JOIN PATTERN

Relational-division-style JOIN (self-join × multiple conditions)

Self-join the same table from multiple perspectives to extract rows satisfying an AND condition
p1 = purchase row for product A p2 = purchase row for product B

Think of the same table from multiple perspectives: SQL JOIN is not limited to connecting different tables; it can place the same table in multiple roles. Treat p1 as “the fact that the user bought product A” and p2 as “the fact that the user bought product B.” Joining them side by side on the same user_id lets one SELECT evaluate the condition “A AND B” across multiple rows.

Choose between this and the HAVING alternative: With two products, either SELF JOIN or HAVING works, but SELF JOIN becomes huge with three or more products. In that case, HAVING COUNT(DISTINCT product_code) = N is easier to maintain. SELF JOIN is more intuitive when you need to see which product combination matched at once.

ANTI-PATTERNS

Try to express “AND” with OR / IN: WHERE product_code IN ('A', 'B') means “A or B.” It also includes user 2 (A only) and user 3 (B only). To evaluate “AND” across multiple rows, you must use SELF JOIN or HAVING.

Forgetting DISTINCT: With this SELF JOIN, user 1 produces one A-row × B-row pair, and user 4 likewise produces one pair. But as users acquire other products such as C, duplicate combinations may produce multiple rows. Make SELECT DISTINCT a habit.

Practical column: Matching multiple conditions

Extracting records that satisfy multiple conditions at once appears frequently in practical data analysis, such as e-commerce recommendations (recommend product B to users who bought product A) and marketing analysis (analyze the LTV of users who joined both campaign A and campaign B). Through this pattern, understand that SQL JOIN can express not only horizontal data combination but also a logical AND across multiple rows.

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.