SQL RIGHT JOIN Explained (and Why Most People Use LEFT JOIN Instead)

A RIGHT JOIN is the mirror image of a LEFT JOIN. It returns every row from the right table (the one after RIGHT JOIN), plus matching rows from the left table. Where there is no match, the left table's columns are NULL.

The example tables#

customers

orders

Syntax#

SELECT columns
FROM left_table
RIGHT JOIN right_table
  ON left_table.column = right_table.column;

RIGHT OUTER JOIN means exactly the same thing.

Example: every order, with its customer if known#

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o
  ON o.customer_id = c.customer_id
ORDER BY o.order_id;

name | order_id | amount
-----+----------+-------
Asha | 101      | 250
Asha | 102      | 120
Ravi | 103      | 560
NULL | 104      | 90

All four orders appear. Order 104's customer (id 5) does not exist, so name is NULL. Meera and John, who have no orders, do not appear: a right join only protects rows of the right table.

RIGHT JOIN is LEFT JOIN with the tables swapped#

This LEFT JOIN returns exactly the same rows:

SELECT c.name, o.order_id, o.amount
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.customer_id
ORDER BY o.order_id;

name | order_id | amount
-----+----------+-------
Asha | 101      | 250
Asha | 102      | 120
Ravi | 103      | 560
NULL | 104      | 90

Because of this, most SQL style guides recommend using only LEFT JOIN. Queries read naturally from the "main" table outward, and you never have to flip directions in your head when a query mixes several joins.

Finding rows with no match#

Just like with LEFT JOIN, keep only rows where the other side is NULL. Orders that point to a missing customer:

SELECT o.order_id, o.customer_id
FROM customers c
RIGHT JOIN orders o
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

order_id | customer_id
---------+------------
104      | 5

This is a quick data-quality check. A foreign key constraint would have prevented the orphan in the first place.

Database support#

RIGHT JOIN is supported by PostgreSQL, MySQL, SQL Server and Oracle. SQLite added it in version 3.39 (2022); older SQLite versions require rewriting it as a LEFT JOIN.

Common mistakes#

Practice#

SELECT o.order_id, c.city
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;

SELECT o.order_id, c.city
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

order_id | city
---------+-------
101      | Mumbai
102      | Mumbai
103      | Delhi
104      | NULL

Key takeaway#