SQL RIGHT JOIN: Syntax, Examples & Practice

The Playground database has an orders table and a users table of customers. Some customers have never ordered. This query starts from orders but still returns every customer, because users is on the right-hand side of a RIGHT JOIN:

SELECT o.id AS order_id, o.total,
       u.id AS user_id, u.first_name
FROM orders o
RIGHT JOIN users u
  ON o.user_id = u.id
WHERE u.id <= 5
ORDER BY u.id, o.id;

▶ Run this query in the SQL Playground

Kenneth has no orders, but he is still in the result: the two columns that come from orders, the left table, are NULL. Michael has three orders and appears three times. Five customers produce 7 rows.

"Right" only means "written after the words RIGHT JOIN". The right table is the one whose rows are all kept; the left table supplies matches where they exist. An INNER JOIN would have dropped Kenneth.

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

Database support

For each row of the right table, the database looks for rows in the left table that satisfy ON:

Left rows that match nothing are dropped. The result therefore never has fewer rows than the right table:

SELECT COUNT(*)             AS result_rows,
       COUNT(DISTINCT u.id) AS customers,
       COUNT(o.id)          AS matched_orders,
       COUNT(*) - COUNT(o.id) AS customers_without_orders
FROM orders o
RIGHT JOIN users u
  ON o.user_id = u.id;

▶ Run this query in the SQL Playground

All 150 customers are present: 200 rows for orders that matched, plus 36 NULL rows for customers who never ordered, 236 in total.

The same join, read from the other side.

A RIGHT JOIN B and B LEFT JOIN A are the same query. Both keep every row of B. Swap the two tables, change the keyword, and nothing else:

-- keeps every customer
SELECT u.first_name, o.id
FROM orders o
RIGHT JOIN users u
  ON o.user_id = u.id;

-- identical result
SELECT u.first_name, o.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id;

SELECT 'orders RIGHT JOIN users' AS query,
       COUNT(*) AS rows_returned, COUNT(o.id) AS matched
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id
UNION ALL
SELECT 'users LEFT JOIN orders', COUNT(*), COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

▶ Run this query in the SQL Playground

Which one should you use?

Most teams write LEFT JOIN everywhere. A reader then knows the first table in FROM is the complete one, and every later join only adds optional detail. A query that mixes LEFT and RIGHT joins is much harder to follow. RIGHT JOIN is still worth knowing for three reasons:

To see all four joins give different answers, both tables need rows the other lacks. Take the customers who ordered in 2023 as the left side and those who ordered in 2024 as the right side:

WITH y2023 AS (
  SELECT DISTINCT user_id FROM orders
  WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
), y2024 AS (
  SELECT DISTINCT user_id FROM orders
  WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
)
SELECT 'INNER JOIN' AS join_type, COUNT(*) AS customers
FROM y2023 a INNER JOIN y2024 b ON a.user_id = b.user_id
UNION ALL
SELECT 'LEFT JOIN', COUNT(*)
FROM y2023 a LEFT JOIN y2024 b ON a.user_id = b.user_id
UNION ALL
SELECT 'RIGHT JOIN', COUNT(*)
FROM y2023 a RIGHT JOIN y2024 b ON a.user_id = b.user_id
UNION ALL
SELECT 'FULL OUTER JOIN', COUNT(*)
FROM y2023 a FULL OUTER JOIN y2024 b ON a.user_id = b.user_id;

▶ Run this query in the SQL Playground

The RIGHT JOIN rows show who is new in 2024: the left column is NULL for customers with no 2023 order.

WITH y2023 AS (
  SELECT DISTINCT user_id FROM orders
  WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
), y2024 AS (
  SELECT DISTINCT user_id FROM orders
  WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
)
SELECT a.user_id AS ordered_2023,
       b.user_id AS ordered_2024
FROM y2023 a
RIGHT JOIN y2024 b
  ON a.user_id = b.user_id
ORDER BY b.user_id
LIMIT 6;

▶ Run this query in the SQL Playground

A FULL OUTER JOIN keeps the unmatched rows of both tables; RIGHT JOIN keeps them for the right table only. The WITH blocks at the top are CTEs, named subqueries that keep the joins readable.

After a RIGHT JOIN, a right row with no match has NULL in every left-table column. Filter on that with IS NULL to list the right rows that have nothing attached. Which products have never been reviewed?

SELECT p.id, p.name, p.category
FROM reviews r
RIGHT JOIN products p
  ON r.product_id = p.id
WHERE r.id IS NULL
ORDER BY p.id;

▶ Run this query in the SQL Playground

27 products have no review. Test the left table's primary key (r.id): it can only be NULL when no row matched.

Checking for orphaned rows

The same pattern is a data-quality check. An order should always belong to a customer; a RIGHT JOIN from users to orders keeps every order and exposes any that point at a customer who does not exist:

SELECT COUNT(*) AS orders_without_a_customer
FROM users u
RIGHT JOIN orders o
  ON u.id = o.user_id
WHERE u.id IS NULL;

▶ Run this query in the SQL Playground

The Playground data is clean, so the count is 0. On imported or migrated data this query is how broken references are usually found.

A condition on the left table belongs in ON, or the join stops keeping every right row.

You want every order with its number of successful payments. The condition p.status = 'success' is about payments, the left table. Compare the two places it can go:

SELECT 'condition in WHERE' AS version, COUNT(DISTINCT o.id) AS orders_returned
FROM payments p
RIGHT JOIN orders o ON p.order_id = o.id
WHERE p.status = 'success'
UNION ALL
SELECT 'condition in ON', COUNT(DISTINCT o.id)
FROM payments p
RIGHT JOIN orders o ON p.order_id = o.id AND p.status = 'success';

▶ Run this query in the SQL Playground

In WHERE, only 99 of 200 orders remain. WHERE runs after the join, and for an order with no payment p.status is NULL, so the row fails the test. In ON, the condition only decides which payments count as a match:

SELECT o.id AS order_id, o.status,
       COUNT(p.id) AS successful_payments
FROM payments p
RIGHT JOIN orders o
  ON p.order_id = o.id
 AND p.status = 'success'
WHERE o.id <= 6
GROUP BY o.id, o.status
ORDER BY o.id;

▶ Run this query in the SQL Playground

A RIGHT JOIN keeps groups that would otherwise have no rows, so zeros show up in a report. How many directors does each department have?

SELECT d.name AS department,
       COUNT(e.id) AS directors
FROM employees e
RIGHT JOIN departments d
  ON e.department_id = d.id
 AND e.title LIKE '%Director%'
GROUP BY d.id, d.name
ORDER BY directors DESC, d.name;

▶ Run this query in the SQL Playground

Five departments have no director and still appear, with 0. Two things make that work: the title condition is in ON, and the count is COUNT(e.id), which ignores NULL. COUNT(*) would report 1 for those departments, because each still has one row. Other aggregates over no rows, such as SUM and AVG, return NULL; wrap them in COALESCE if you want 0.

Joins are applied in the order they are written. Each RIGHT JOIN keeps every row of the table it introduces, so in a chain the last table is the one that is complete:

SELECT u.id AS user_id, u.first_name,
       o.id AS order_id, oi.product_id, oi.quantity
FROM order_items oi
RIGHT JOIN orders o
  ON oi.order_id = o.id
RIGHT JOIN users u
  ON o.user_id = u.id
WHERE u.id <= 3
ORDER BY u.id, o.id, oi.id;

▶ Run this query in the SQL Playground

Kenneth has no order, so both the order and the item columns are NULL, but he is kept. The same report written with LEFT JOINs starts from the table that must be complete, which is why most people find it easier to read:

SELECT u.id AS user_id, u.first_name,
       o.id AS order_id, oi.product_id, oi.quantity
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
LEFT JOIN order_items oi
  ON oi.order_id = o.id
WHERE u.id <= 3
ORDER BY u.id, o.id, oi.id;

Every row of this table could be written as a LEFT JOIN with the tables in the other order. Spreadsheet and BI tools offer the same join as "Right Outer" in their merge dialogs.

A RIGHT JOIN costs the same as the equivalent LEFT JOIN. Database optimisers treat the two as the same operation, so choosing between them is a readability decision, not a speed one. What matters is the usual join advice:

7 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Using RIGHT JOIN, list customers 10 to 20 with the id and status of each of their orders. Customers with no orders must appear. Table: orders, users

   Show solution

   SQLite · runs in the Playground

   SELECT u.id AS user_id, u.first_name,
          o.id AS order_id, o.status
   FROM orders o
   RIGHT JOIN users u
     ON o.user_id = u.id
   WHERE u.id BETWEEN 10 AND 20
   ORDER BY u.id, o.id;

   ▶ Run this query in the SQL Playground

   users is on the right, so all eleven customers are kept; the filter on the right table is safe in WHERE. The result has 14 rows.

2. 2Easy Using RIGHT JOIN, find any product that has never been ordered. Table: order_items, products

   Show solution

   SQLite · runs in the Playground

   SELECT p.id, p.name, p.category
   FROM order_items oi
   RIGHT JOIN products p
     ON oi.product_id = p.id
   WHERE oi.id IS NULL;

   ▶ Run this query in the SQL Playground

   RIGHT JOIN keeps every product and oi.id IS NULL leaves only those with no order item: 1 product.

3. 3Medium Rewrite this as a RIGHT JOIN that returns the same rows: users LEFT JOIN orders, showing each customer id and their number of orders, for customers 1 to 8. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, COUNT(o.id) AS orders
   FROM orders o
   RIGHT JOIN users u
     ON o.user_id = u.id
   WHERE u.id <= 8
   GROUP BY u.id
   ORDER BY u.id;

   ▶ Run this query in the SQL Playground

   Swap the two tables and change LEFT to RIGHT. The ON condition, the columns and the grouping stay the same.

4. 4Medium Using RIGHT JOIN, count the orders that have no payment at all. Table: payments, orders

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS unpaid_orders
   FROM payments p
   RIGHT JOIN orders o
     ON p.order_id = o.id
   WHERE p.id IS NULL;

   ▶ Run this query in the SQL Playground

   After the IS NULL filter every remaining row is one order with no payment, so COUNT(*) is right here.

5. 5Medium For each product category, count the products that have no reviews. Use RIGHT JOIN. Table: reviews, products

   Show solution

   SQLite · runs in the Playground

   SELECT p.category,
          COUNT(*) AS products_without_reviews
   FROM reviews r
   RIGHT JOIN products p
     ON r.product_id = p.id
   WHERE r.id IS NULL
   GROUP BY p.category
   ORDER BY products_without_reviews DESC, p.category;

   ▶ Run this query in the SQL Playground

   The anti-join leaves one row per unreviewed product; grouping by category counts them. 10 categories have at least one.

6. 6Challenging For every department, count the employees hired in 2024. Departments with none must show 0. Use RIGHT JOIN. Table: employees, departments

   Show solution

   SQLite · runs in the Playground

   SELECT d.name AS department,
          COUNT(e.id) AS hired_2024
   FROM employees e
   RIGHT JOIN departments d
     ON e.department_id = d.id
    AND e.hire_date >= '2024-01-01'
    AND e.hire_date <  '2025-01-01'
   GROUP BY d.id, d.name
   ORDER BY hired_2024 DESC, d.name;

   ▶ Run this query in the SQL Playground

   The date range is a condition on the left table, so it goes in ON. In WHERE it would remove departments with no 2024 hire.

7. 7Challenging Find the customers who ordered in 2024 but not in 2023. Join the 2023 buyers to the 2024 buyers with a RIGHT JOIN. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH y2023 AS (
     SELECT DISTINCT user_id FROM orders
     WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01'
   ), y2024 AS (
     SELECT DISTINCT user_id FROM orders
     WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
   )
   SELECT b.user_id
   FROM y2023 a
   RIGHT JOIN y2024 b
     ON a.user_id = b.user_id
   WHERE a.user_id IS NULL
   ORDER BY b.user_id;

   ▶ Run this query in the SQL Playground

   The RIGHT JOIN keeps every 2024 buyer; a.user_id IS NULL keeps those with no 2023 match. 37 customers are new in 2024.