RIGHT JOIN

Keep all right rows — the mirror of LEFT JOIN

Intermediate 12 minRIGHT JOINOUTER JOIN

The Mirror of LEFT JOIN

RIGHT JOIN returns all rows from the right table, plus matched rows from the left. Unmatched left columns fill with NULL. Functionally, it is identical to LEFT JOIN with the two tables swapped — but you will encounter both forms in real codebases.

LEFT and RIGHT are mirror images — A LEFT JOIN B and B RIGHT JOIN A return the exact same rows. The choice is purely stylistic, but consistency within a query matters.

Why anyone uses RIGHT JOIN — Sometimes the natural reading order of a query (left-to-right, source → target) lines up with RIGHT JOIN — for example, "from each event, attach the user who triggered it, but keep all users even those with no events." Writing events RIGHT JOIN users reads more naturally than reordering the FROM clause.

Style convention — Most teams default to LEFT JOIN everywhere because flipping table order is a small ask in exchange for one consistent mental model. But RIGHT JOIN is valid SQL and shows up in legacy code and tool-generated queries — knowing it is reading-list essential.

SQLite gotcha — RIGHT JOIN was added to SQLite in version 3.39 (2022). Older versions raise a syntax error. The in-browser engine here is new enough to support it. Postgres, MySQL 8+, Snowflake, and BigQuery have always supported it.

Knowledge Canvas

How RIGHT JOIN Works

Mirror image of LEFT JOIN

ALL right rows survive — unmatched left columns fill with NULL
Right table = preserved table
A LEFT JOIN B is identical to B RIGHT JOIN A
RIGHT OUTER JOIN = RIGHT JOIN (OUTER is optional)

LEFT vs RIGHT — Style Choice

Same result, different reading order

A LEFT JOIN B ON …

B RIGHT JOIN A ON …

Preserves: A (left)

Preserves: A (right)

Convention: most teams default here

Convention: rare in human-written SQL

When RIGHT JOIN Reads Better

The narrow case for using it

Source-→-target queries: events RIGHT JOIN users when "from each event, attach user, keep all users"
Tool-generated SQL — some BI tools default to RIGHT JOIN
Legacy codebases — recognizing it is reading-list essential
When the FROM clause already lists the target on the left

RIGHT JOIN Pitfalls

Watch out for

SQLite < 3.39 raises a syntax error — older builds need LEFT JOIN rewrite
Mixing LEFT and RIGHT in one query is a readability nightmare
Engineers reading your code will mentally rewrite RIGHT to LEFT — pick one and stick with it
NULL columns from left table behave the same as in LEFT JOIN — same anti-join trick works

Critical Rules

Rules of thumb

Pick LEFT or RIGHT for a query — never mix in the same statement
If converting LEFT → RIGHT, swap the two table names in FROM/JOIN
COUNT(left.id) returns 0 for unmatched right rows — same as LEFT JOIN
NULL in the join key still never matches — both sides must respect that

Syntax

Syntax Template

1-- Keep all right rows

2SELECT

3 a.col,

4 b.col

5FROM table_a a

6RIGHT JOIN table_b b

7 ON a.key = b.key;

9-- Equivalent rewrite using LEFT JOIN

10SELECT

11 a.col,

12 b.col

13FROM table_b b

14LEFT JOIN table_a a

15 ON a.key = b.key;

RIGHT JOINAll right rows + matched left rows (or NULL)

RewriteSwap tables and replace with LEFT JOIN — same result

Sample Data

products

10 rows

id	name	category	price
P101	Wireless Mouse	Electronics	29.99
P102	Office Desk	Furniture	189
P103	LED Desk Lamp	Furniture	45
P104	USB-C Cable	Electronics	12.5
P105	Bluetooth Speaker	Electronics	59.99
P106	Office Chair	Furniture	129
P107	Coffee Maker	Appliances	79.99
P108	Notebook Set	Stationery	18.99
P109	Wireless Headphones	Electronics	159
P110	Standing Desk	Furniture	425

reviews

12 rows

id	product_id	customer_id	rating
7001	P101	C1001	5
7002	P101	C1003	4
7003	P101	C1005	3
7004	P102	C1002	5
7005	P102	C1006	4
7006	P103	C1004	4
7007	P104	C1007	5
7008	P105	C1009	5
7009	P105	C1012	4
7010	P107	C1008	3
7011	P108	C1011	5
7012	P109	C1001	5

Worked Example

Show every product with its review count, including products with zero reviews.

SQL

1SELECT

2 p.name,

3 COUNT(rv.id) AS review_count

4FROM reviews rv

5RIGHT JOIN products p

6 ON p.id = rv.product_id

7GROUP BY

8 p.id,

9 p.name

10ORDER BY

11 review_count DESC,

12 p.name;

RIGHT JOIN keeps every row from products (the right side) — including Office Chair and Standing Desk, which have zero reviews. COUNT(rv.id) correctly returns 0 for those rows because COUNT ignores NULLs. The same query with INNER JOIN would silently drop those two products from the result.

Output

10 rows

name	review_count
Wireless Mouse	3
Bluetooth Speaker	2
Office Desk	2
Coffee Maker	1
LED Desk Lamp	1
Notebook Set	1
USB-C Cable	1
Wireless Headphones	1
Office Chair	0
Standing Desk	0

Common Mistakes

❌

Confusing which side is preserved

SQL

1SELECT

2 p.name,

3 COUNT(rv.id) AS review_count

4FROM products p

5RIGHT JOIN reviews rv

6 ON p.id = rv.product_id

7GROUP BY

8 p.id,

9 p.name;

Here reviews is the right side, so RIGHT JOIN keeps all reviews — but products becomes the side that drops unmatched rows. Office Chair and Standing Desk vanish from the result because they appear in the LEFT side that gets filtered to matches only.

Decide which table you want to preserve, then put it on the side that matches the JOIN keyword. RIGHT JOIN preserves the right side; LEFT JOIN preserves the left.

❌

Forgetting the SQLite version requirement

RIGHT JOIN was added to SQLite in version 3.39 (2022). Older builds (Android system SQLite, some embedded environments) raise a syntax error. Postgres, MySQL 8+, Snowflake, and BigQuery all support it without issue.

On older SQLite, rewrite as LEFT JOIN with the tables swapped. The two are mathematically identical.

Key Concepts

1ALL right rows survive — unmatched left columns fill with NULL

2RIGHT JOIN is just LEFT JOIN with the table order flipped

3Most engineers prefer LEFT JOIN — but you must read RIGHT JOINs in real codebases

4Not supported in SQLite < 3.39 — modern engines (Postgres, MySQL 8+, Snowflake, BigQuery) all support it

Pro Tip

You will read RIGHT JOIN in production SQL whether you write it yourself or not. Recognizing it instantly — and being able to mentally rewrite it as LEFT JOIN with swapped tables — saves debugging time.

When to Use

Reading legacy queries, tool-generated SQL (some BI tools default to RIGHT JOIN), or query patterns where the "preserve everything on this side" table reads more naturally on the right.

Challenge

Solve the problem below

List every customer with their order count, including customers who have placed zero orders. Use RIGHT JOIN. Order by order_count DESC then name.

Your Query

LEFT JOIN

FULL OUTER JOIN