|$ curl https://forge-ai.dev/api/markdown?path=docs/databases/sql-joins

$cat docs/sql-joins-deep-dive.md

updated Recently·40 min read·published

SQL JOINs Deep Dive

◆SQL◆Databases◆Intermediate◆Intermediate🎯Free Tools

Introduction

JOINs combine rows from two or more tables based on related columns. They are the cornerstone of relational databases — without JOINs, you would need to store all data in a single table, leading to massive redundancy and data integrity issues.

This guide covers every JOIN type with visual explanations, real-world examples, and performance considerations. By the end, you will know exactly when to use each JOIN type and how to avoid common pitfalls.

INNER JOIN — Only Matching Rows

INNER JOIN returns only rows that have matching values in both tables. If a row in the left table has no match in the right table (or vice versa), it is excluded from the result.

inner-join.sql

SQL

1	-- INNER JOIN: Only users who have placed orders
2	SELECT
3	u.id AS user_id,
4	u.name,
5	u.email,
6	o.id AS order_id,
7	o.total,
8	o.created_at AS order_date
9	FROM users u
10	INNER JOIN orders o ON o.user_id = u.id;
11
12	-- INNER JOIN with multiple tables
13	SELECT
14	u.name,
15	o.id AS order_id,
16	p.name AS product_name,
17	oi.quantity,
18	oi.unit_price
19	FROM users u
20	INNER JOIN orders o ON o.user_id = u.id
21	INNER JOIN order_items oi ON oi.order_id = o.id
22	INNER JOIN products p ON p.id = oi.product_id;
23
24	-- INNER JOIN with filtering
25	SELECT
26	u.name,
27	o.id AS order_id,
28	o.total
29	FROM users u
30	INNER JOIN orders o ON o.user_id = u.id
31	WHERE o.status = 'completed'
32	AND o.total > 100
33	ORDER BY o.total DESC;
34
35	-- INNER JOIN with aggregate
36	SELECT
37	u.name,
38	COUNT(o.id) AS total_orders,
39	SUM(o.total) AS total_spent
40	FROM users u
41	INNER JOIN orders o ON o.user_id = u.id
42	GROUP BY u.id, u.name
43	HAVING COUNT(o.id) >= 3;

📝

note

INNER JOIN is the default JOIN type. If you write just JOIN, it means INNER JOIN.

LEFT JOIN — All Left Rows + Matches

LEFT JOIN returns all rows from the left table, and matching rows from the right table. If there is no match in the right table, NULL values are returned for right table columns. This is the most commonly used JOIN type.

left-join.sql

SQL

1	-- LEFT JOIN: All users, with their orders (if any)
2	SELECT
3	u.id AS user_id,
4	u.name,
5	u.email,
6	o.id AS order_id,
7	o.total
8	FROM users u
9	LEFT JOIN orders o ON o.user_id = u.id;
10
11	-- Find users with NO orders (anti-join pattern)
12	SELECT u.id, u.name, u.email
13	FROM users u
14	LEFT JOIN orders o ON o.user_id = u.id
15	WHERE o.id IS NULL;
16
17	-- LEFT JOIN with COUNT (count 0 for users without orders)
18	SELECT
19	u.name,
20	COUNT(o.id) AS order_count
21	FROM users u
22	LEFT JOIN orders o ON o.user_id = u.id
23	GROUP BY u.id, u.name;
24
25	-- LEFT JOIN with COALESCE (default values for NULL)
26	SELECT
27	u.name,
28	COALESCE(SUM(o.total), 0) AS total_spent,
29	COALESCE(MAX(o.created_at), 'Never') AS last_order
30	FROM users u
31	LEFT JOIN orders o ON o.user_id = u.id
32	GROUP BY u.id, u.name;
33
34	-- LEFT JOIN with multiple tables
35	SELECT
36	u.name,
37	o.id AS order_id,
38	COALESCE(oi_count.item_count, 0) AS items
39	FROM users u
40	LEFT JOIN orders o ON o.user_id = u.id
41	LEFT JOIN (
42	SELECT order_id, COUNT(*) AS item_count
43	FROM order_items
44	GROUP BY order_id
45	) oi_count ON oi_count.order_id = o.id;
46
47	-- LEFT JOIN with filtering on right table (careful!)
48	-- This filters AFTER the join, so users without orders are still included
49	SELECT
50	u.name,
51	o.id AS order_id,
52	o.total
53	FROM users u
54	LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'completed'
55	WHERE u.status = 'active';

⚠

warning

Filtering on the right table in the WHERE clause turns a LEFT JOIN into an INNER JOIN. To keep LEFT JOIN semantics, filter in the ON clause instead.

RIGHT JOIN — All Right Rows + Matches

RIGHT JOIN returns all rows from the right table, and matching rows from the left table. It is the mirror image of LEFT JOIN. You can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the tables.

right-join.sql

SQL

1	-- RIGHT JOIN: All orders, with user info (if user exists)
2	SELECT
3	u.name,
4	o.id AS order_id,
5	o.total,
6	o.status
7	FROM users u
8	RIGHT JOIN orders o ON o.user_id = u.id;
9
10	-- Same as LEFT JOIN with tables swapped
11	SELECT
12	u.name,
13	o.id AS order_id,
14	o.total
15	FROM orders o
16	LEFT JOIN users u ON u.id = o.user_id;
17
18	-- Find orders with invalid user_id (orphaned orders)
19	SELECT o.id, o.user_id, o.total
20	FROM users u
21	RIGHT JOIN orders o ON o.user_id = u.id
22	WHERE u.id IS NULL;
23
24	-- RIGHT JOIN with aggregates
25	SELECT
26	u.name,
27	COUNT(o.id) AS order_count
28	FROM users u
29	RIGHT JOIN orders o ON o.user_id = u.id
30	GROUP BY u.id, u.name;

ℹ

info

Most developers prefer LEFT JOIN over RIGHT JOIN because it is easier to read. You can always convert RIGHT JOIN to LEFT JOIN by swapping table order.

FULL OUTER JOIN — All Rows from Both Tables

FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match. It is useful for finding mismatched data between tables or for data reconciliation.

full-outer-join.sql

SQL

1	-- FULL OUTER JOIN: All users and all orders
2	SELECT
3	u.id AS user_id,
4	u.name,
5	o.id AS order_id,
6	o.total
7	FROM users u
8	FULL OUTER JOIN orders o ON o.user_id = u.id;
9
10	-- Find unmatched rows (data reconciliation)
11	-- Users without orders OR orders without users
12	SELECT
13	COALESCE(u.id, o.user_id) AS id,
14	u.name,
15	o.id AS order_id,
16	CASE
17	WHEN u.id IS NULL THEN 'Orphaned order'
18	WHEN o.id IS NULL THEN 'No orders'
19	ELSE 'Matched'
20	END AS status
21	FROM users u
22	FULL OUTER JOIN orders o ON o.user_id = u.id
23	WHERE u.id IS NULL OR o.id IS NULL;
24
25	-- FULL OUTER JOIN for data comparison
26	SELECT
27	a.id AS source_id,
28	a.name AS source_name,
29	b.id AS target_id,
30	b.name AS target_name,
31	CASE
32	WHEN a.id IS NULL THEN 'Only in target'
33	WHEN b.id IS NULL THEN 'Only in source'
34	WHEN a.name != b.name THEN 'Name mismatch'
35	ELSE 'Match'
36	END AS comparison
37	FROM source_users a
38	FULL OUTER JOIN target_users b ON b.id = a.id;

CROSS JOIN — Cartesian Product

CROSS JOIN produces the Cartesian product of both tables — every row from the first table is combined with every row from the second table. Use it sparingly, as it can produce enormous result sets.

cross-join.sql

SQL

1	-- CROSS JOIN: Every combination of products and colors
2	SELECT
3	p.name AS product,
4	c.name AS color
5	FROM products p
6	CROSS JOIN colors c;
7
8	-- Generate all date-store combinations for reporting
9	SELECT
10	d.date,
11	s.name AS store_name,
12	0 AS revenue -- Placeholder for missing data
13	FROM dates d
14	CROSS JOIN stores s
15	WHERE d.date BETWEEN '2025-01-01' AND '2025-01-31';
16
17	-- CROSS JOIN with LEFT JOIN to fill gaps
18	SELECT
19	d.date,
20	s.name AS store_name,
21	COALESCE(SUM(sales.amount), 0) AS revenue
22	FROM dates d
23	CROSS JOIN stores s
24	LEFT JOIN sales ON sales.date = d.date AND sales.store_id = s.id
25	GROUP BY d.date, s.name
26	ORDER BY d.date, s.name;
27
28	-- Generate test data combinations
29	SELECT
30	u.id AS user_id,
31	p.id AS product_id
32	FROM users u
33	CROSS JOIN products p
34	WHERE u.country = 'USA'
35	AND p.category = 'electronics';

danger

CROSS JOIN on large tables produces massive result sets. A table with 10,000 rows crossed with another 10,000 rows produces 100 million rows. Always add WHERE clauses to limit the result.

SELF JOIN — Table Joined with Itself

SELF JOIN joins a table with itself. It is useful for hierarchical data, finding duplicates, or comparing rows within the same table.

self-join.sql

SQL

1	-- SELF JOIN: Employee and manager hierarchy
2	SELECT
3	e.id AS employee_id,
4	e.name AS employee_name,
5	e.role AS employee_role,
6	m.name AS manager_name,
7	m.role AS manager_role
8	FROM employees e
9	LEFT JOIN employees m ON e.manager_id = m.id;
10
11	-- Find employees at the same level (same manager)
12	SELECT
13	e1.name AS employee1,
14	e2.name AS employee2,
15	m.name AS manager
16	FROM employees e1
17	JOIN employees e2 ON e1.manager_id = e2.manager_id AND e1.id < e2.id
18	JOIN employees m ON e1.manager_id = m.id;
19
20	-- Find duplicate emails
21	SELECT
22	a.id AS id1,
23	a.name AS name1,
24	b.id AS id2,
25	b.name AS name2,
26	a.email
27	FROM users a
28	JOIN users b ON a.email = b.email AND a.id < b.id;
29
30	-- Find products in the same category with different prices
31	SELECT
32	p1.name AS product1,
33	p1.price AS price1,
34	p2.name AS product2,
35	p2.price AS price2,
36	ABS(p1.price - p2.price) AS price_diff
37	FROM products p1
38	JOIN products p2 ON p1.category = p2.category AND p1.id < p2.id
39	WHERE ABS(p1.price - p2.price) > 100
40	ORDER BY price_diff DESC;
41
42	-- Sequential data comparison (find consecutive events)
43	SELECT
44	a.id AS event1_id,
45	a.created_at AS event1_time,
46	b.id AS event2_id,
47	b.created_at AS event2_time,
48	b.created_at - a.created_at AS time_diff
49	FROM events a
50	JOIN events b ON a.id = b.id - 1
51	ORDER BY a.created_at;

JOIN Performance Tips

join-performance.sql

SQL

1	-- 1. Always index JOIN columns
2	CREATE INDEX idx_orders_user_id ON orders(user_id);
3	CREATE INDEX idx_order_items_order_id ON order_items(order_id);
4	CREATE INDEX idx_order_items_product_id ON order_items(product_id);
5
6	-- 2. Join on indexed columns only
7	-- BAD: Join on non-indexed column
8	SELECT * FROM users u
9	JOIN orders o ON o.user_email = u.email; -- If email is not indexed
10
11	-- GOOD: Join on indexed primary key
12	SELECT * FROM users u
13	JOIN orders o ON o.user_id = u.id; -- user_id is indexed
14
15	-- 3. Filter early (in ON clause or WHERE)
16	-- BAD: Filter after joining all rows
17	SELECT * FROM users u
18	LEFT JOIN orders o ON o.user_id = u.id
19	WHERE o.status = 'completed';
20
21	-- GOOD: Filter in ON clause (for LEFT JOIN)
22	SELECT * FROM users u
23	LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'completed';
24
25	-- 4. Use EXISTS instead of JOIN for existence checks
26	-- BAD: JOIN just to check existence
27	SELECT DISTINCT u.name
28	FROM users u
29	JOIN orders o ON o.user_id = u.id
30	WHERE o.total > 1000;
31
32	-- GOOD: EXISTS is faster for existence checks
33	SELECT u.name
34	FROM users u
35	WHERE EXISTS (
36	SELECT 1 FROM orders o
37	WHERE o.user_id = u.id AND o.total > 1000
38	);
39
40	-- 5. Avoid joining on expressions
41	-- BAD: Function on join column prevents index usage
42	SELECT * FROM users u
43	JOIN orders o ON DATE(o.created_at) = DATE(u.created_at);
44
45	-- GOOD: Use range comparison instead
46	SELECT * FROM users u
47	JOIN orders o ON o.created_at >= u.created_at
48	AND o.created_at < u.created_at + INTERVAL '1 day';

Summary

JOIN Type	Returns	Use Case
INNER JOIN	Only matching rows	Find users who have orders
LEFT JOIN	All left rows + matches	Find all users, with or without orders
RIGHT JOIN	All right rows + matches	Find all orders, with or without users
FULL OUTER JOIN	All rows from both tables	Data reconciliation, find mismatches
CROSS JOIN	Cartesian product	Generate all combinations, fill gaps
SELF JOIN	Table joined with itself	Hierarchies, duplicates, comparisons

X GitHub YouTube Instagram Facebook Discord Slack Telegram Buy me a coffee Ko-fi Sponsor