Lesson Beginner

RIGHT JOIN

RIGHT JOIN keeps every row from the right table and fills left-side columns with NULL when there's no match.
It's the mirror image of LEFT JOIN, and almost nobody writes it in practice.

Syntax #

sql

SELECT
  o.id AS order_id,
  u.email,
  o.total
FROM orders AS o
RIGHT JOIN users AS u
  ON o.user_id = u.id;

Every users row appears at least once. Users with orders get one row per order; users with no orders get one row with NULL for every o.* column.

Prefer LEFT JOIN #

Tip

RIGHT JOIN and LEFT JOIN are equivalent under table swap. Any RIGHT JOIN can be rewritten as a LEFT JOIN by reordering the tables, and that's almost always easier to read. The convention in analytics and data engineering teams is to stick to LEFT JOIN so the "driving table" is always the one listed first.

sql

-- These two queries return identical rows (possibly in different order)
SELECT o.id, u.email
FROM orders AS o
RIGHT JOIN users AS u ON o.user_id = u.id;

SELECT o.id, u.email
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

When You Might See It #

RIGHT JOIN tends to appear when:

A query was built by chaining joins and the author didn't want to reorder the FROM clause.
A generated query (from an ORM or BI tool) encodes direction.
The author is deliberately mirroring another team's convention.

In your own queries, just pick LEFT JOIN and put the "keep everything from" table first.

Try It #

The same user-centric query, written with RIGHT JOIN. Then rewrite it as LEFT JOIN and note they return the same rows.

Loading SQL editor...

Practice #

Rewrite the RIGHT JOIN below as a LEFT JOIN that returns the exact same rows. Output columns: user_id, email, order_id, total. No ordering requirement.

Starting query:

sql

SELECT u.id AS user_id, u.email, o.id AS order_id, o.total
FROM orders AS o
RIGHT JOIN users AS u ON o.user_id = u.id;

Loading SQL editor...

Mistakes to Watch For #

Mixing LEFT JOIN and RIGHT JOIN in the same query, where the driving side becomes hard to track. Pick one direction and stick to it.
Filtering the left-side table in WHERE after a RIGHT JOIN — same trap as LEFT JOIN, just flipped. Put the condition in ON to keep right-side-only rows.
Assuming RIGHT JOIN is needed for some case LEFT JOIN can't handle. There isn't one.

Knowledge check #

3 questions

0 / 3 answered

SELECT * FROM A RIGHT JOIN B ON ... is equivalent to which form using LEFT JOIN?
Why is RIGHT JOIN rare in production code?
A RIGHT JOIN B returns:

Next Step #

Continue to FULL OUTER JOIN to keep unmatched rows from both sides at once.