SQL 36 🛢️ Right Side Data with RIGHT JOIN - KandZ

The RIGHT JOIN is the mirror image of the LEFT JOIN. It returns all rows from the right table and the matching rows from the left table. When a right row has no match in the left table, the left table’s columns are filled with NULL, and the row still appears in the result. The keyword that distinguishes it from the LEFT JOIN is which table is preserved: the left join keeps the left table’s rows, and the right join keeps the right table’s rows.

The RIGHT JOIN is functionally equivalent to a LEFT JOIN with the tables reversed. Because of this equivalence, many developers prefer to use only LEFT JOINs and rewrite right joins by swapping the table order. The result is the same, and the query reads more consistently. But the RIGHT JOIN exists in standard SQL, and understanding it is necessary for reading queries written by others. Some queries are naturally expressed from the perspective of the right table, and forcing them into a left join can make the intent less clear.

This chapter covers the syntax of RIGHT JOIN, the behavior with unmatched rows, the relationship to LEFT JOIN, the patterns for using it, and the cases where rewriting as a LEFT JOIN is preferable.

Key point: RIGHT JOIN returns all rows from the right table and the matching rows from the left table. Unmatched right rows appear with NULL in the left columns. A RIGHT JOIN is equivalent to a LEFT JOIN with the tables swapped. Most developers prefer LEFT JOIN for consistency, but RIGHT JOIN is useful when the right table is the primary subject of the query.

Why RIGHT JOIN exists

The symmetry problem. SQL defines both left and right outer joins because a join has two sides, and either side can be preserved. The LEFT JOIN preserves the first table, and the RIGHT JOIN preserves the second. The language is symmetric, even if usage is not.

The readability problem. A query is easier to read when the preserved table is named first. If the preserved table is the one on the right side of the join, using a RIGHT JOIN keeps the natural reading order. Rewriting as a LEFT JOIN requires swapping the tables, which changes the order the reader sees.

The legacy problem. Existing queries and documentation use RIGHT JOIN. Understanding it is necessary for reading code that was written before the convention of “use LEFT JOIN only” became common.

The equivalence problem. Because RIGHT JOIN and LEFT JOIN are equivalent under table swapping, the choice is a matter of style, not capability. The database optimizes both the same way, and the result is identical.

a. Basic RIGHT JOIN syntax

The syntax places the left table first, then RIGHT JOIN and the right table, with the matching condition in ON.

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

The right table is orders, and its rows are preserved. Every order appears in the result, including orders whose customer_id does not match any customer. For those orders, the name column is NULL.

The RIGHT OUTER JOIN spelling is equivalent. The OUTER keyword is optional.

FROM customers c
RIGHT OUTER JOIN orders o ON c.customer_id = o.customer_id

The right table is the one named after the RIGHT JOIN keyword. Its rows are preserved.

b. The shape of NULL results

When a right row has no match, every column from the left table is NULL.

name	order_id	total
Alice	101	250.00
Alice	102	180.00
Bob	103	320.00
David	104	95.00
NULL	105	500.00

Order 105 has a customer_id that does not exist in the customers table, so the name column is NULL. The order columns are populated because the row comes from the right table.

This is the mirror of the LEFT JOIN behavior. In a LEFT JOIN, unmatched left rows produce NULLs in the right columns. In a RIGHT JOIN, unmatched right rows produce NULLs in the left columns.

c. RIGHT JOIN and LEFT JOIN equivalence

A RIGHT JOIN is equivalent to a LEFT JOIN with the tables reversed. The two queries produce identical results.

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

-- Equivalent LEFT JOIN
SELECT c.name, o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Both return every order, with the customer name when it exists and NULL when it does not. The column order in the SELECT list is the same, and the result rows are the same. The only difference is which table is named first.

Because of this equivalence, the choice between RIGHT JOIN and LEFT JOIN is a matter of which table the reader sees first. If the preserved table should be named first, use a LEFT JOIN. If the preserved table is naturally second, the RIGHT JOIN is an option, though many teams prefer to swap and use a LEFT JOIN.

d. When RIGHT JOIN reads naturally

The RIGHT JOIN reads naturally when the right table is the primary subject of the query. Consider a query that lists every order, with the customer attached if there is one. The orders are the subject; the customer is the attachment.

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

The orders table is the subject, and it is named second. The query lists each order and its customer. Reading it as “from customers, right join orders” is awkward, but the result is what is wanted.

The same query written as a LEFT JOIN:

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

This version names orders first, which matches the subject of the query. It is arguably more readable, and it is the form most developers would write.

The RIGHT JOIN is appropriate when the query’s structure comes from an existing LEFT JOIN that is being reversed, or when a code generator produces it. In hand-written code, the LEFT JOIN with the preserved table named first is the more common convention.

e. Detecting unmatched rows

A RIGHT JOIN with WHERE left.key IS NULL finds right rows with no match in the left table.

-- Orders with no matching customer
SELECT o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

The RIGHT JOIN preserves all orders, and the IS NULL filter keeps only those with no matching customer. This is the mirror of the LEFT JOIN pattern for detecting unmatched left rows.

The equivalent LEFT JOIN:

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

Both queries return the same rows. The LEFT JOIN version names the preserved table first, which is the more common convention.

A NOT EXISTS subquery is another alternative:

SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);

All three forms produce the same result. The choice is a matter of readability and, in some databases, performance.

f. Chaining RIGHT JOINs with other joins

A RIGHT JOIN can be combined with other joins, but the mixing of left and right outer joins in a single query is confusing and should be avoided. If the query needs to preserve rows from multiple tables, a FULL OUTER JOIN or a UNION of LEFT and RIGHT joins is clearer.

-- Avoid mixing LEFT and RIGHT in one query
SELECT ...
FROM a
LEFT JOIN b ON ...
RIGHT JOIN c ON ...  -- confusing

If the query requires preserving the right table of one join and the left table of another, the joins are unlikely to be expressible as a single query without subqueries or a UNION. The mixing of outer join directions makes the result hard to reason about, and the query is better restructured.

The common pattern is to use only LEFT JOINs throughout a query. If a table needs to be preserved on the right side, the tables are swapped, and the join becomes a LEFT JOIN. This keeps the query’s semantics consistent and readable.

Complete Example Session

-- ============================================
-- PART 1: CREATE SAMPLE TABLES
-- ============================================
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        VARCHAR(50)
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total       NUMERIC(10, 2)
);

INSERT INTO customers VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Carol');

INSERT INTO orders VALUES
(101, 1, 250.00),
(102, 1, 180.00),
(103, 2, 320.00),
(104, 9, 500.00);

-- ============================================
-- PART 2: BASIC RIGHT JOIN
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
-- Returns every order; order 104 has NULL name

-- ============================================
-- PART 3: EQUIVALENT LEFT JOIN
-- ============================================
SELECT c.name, o.order_id, o.total
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- Same result, different table order

-- ============================================
-- PART 4: FIND UNMATCHED RIGHT ROWS
-- ============================================
SELECT o.order_id, o.total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- Returns: order 104 (customer 9 does not exist)

-- ============================================
-- PART 5: SAME RESULT WITH LEFT JOIN
-- ============================================
SELECT o.order_id, o.total
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;
-- Same result

-- ============================================
-- PART 6: SAME RESULT WITH NOT EXISTS
-- ============================================
SELECT o.order_id, o.total
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);
-- Same result

-- ============================================
-- PART 7: RIGHT JOIN WITH AGGREGATE
-- ============================================
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;
-- NULL name appears for orders with no customer

-- ============================================
-- PART 8: COUNTING ONLY MATCHED ROWS
-- ============================================
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NOT NULL
GROUP BY c.name;
-- Excludes unmatched orders

-- ============================================
-- PART 9: FILTER IN WHERE BREAKS OUTER
-- ============================================
SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 200;
-- Unmatched right rows with total <= 200 or NULL are excluded

-- ============================================
-- PART 10: PREFERRED FORM — LEFT JOIN WITH SWAPPED TABLES
-- ============================================
SELECT o.order_id, o.total, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- Same result, preserved table named first

These ten parts cover creating the tables, a basic RIGHT JOIN, the equivalent LEFT JOIN, detecting unmatched right rows, the same result with LEFT JOIN, the same result with NOT EXISTS, a RIGHT JOIN with an aggregate, counting only matched rows, filtering in WHERE, and the preferred LEFT JOIN form.

Quick Reference

RIGHT JOIN Syntax

Form	Example
Explicit	`FROM a RIGHT JOIN b ON a.id = b.id`
With OUTER	`FROM a RIGHT OUTER JOIN b ON a.id = b.id`

RIGHT JOIN vs LEFT JOIN

Aspect	RIGHT JOIN	LEFT JOIN
Preserves	Right table	Left table
Unmatched NULLs	In left columns	In right columns
Equivalent to	LEFT JOIN with tables swapped	RIGHT JOIN with tables swapped
Convention	Less common	More common

Unmatched Detection

Pattern	Purpose
`RIGHT JOIN ... WHERE left.key IS NULL`	Right rows with no match
`LEFT JOIN ... WHERE right.key IS NULL`	Left rows with no match
`NOT EXISTS (SELECT 1 FROM left WHERE ...)`	Either direction

Aggregation

Function	Result on unmatched right rows
`COUNT(*)`	Counts the row (1)
`COUNT(left.key)`	Counts only matches (0 if none)
`SUM(left.col)`	NULL if no matches
`COALESCE(SUM(left.col), 0)`	Zero if no matches

Best Practices

✅ Do This:

-- Prefer LEFT JOIN with the preserved table first
SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

-- Use NOT EXISTS for unmatched detection
SELECT o.order_id FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);

-- Keep the join direction consistent in a query

❌ Don’t Do This:

-- Mix LEFT and RIGHT joins in one query
FROM a LEFT JOIN b ON ... RIGHT JOIN c ON ...  -- ❌ confusing

-- Put a right-table filter in WHERE on a RIGHT JOIN
RIGHT JOIN orders o ON ... WHERE o.total > 200;  -- ❌ breaks outer

-- Use RIGHT JOIN when a LEFT JOIN reads better
SELECT c.name, o.order_id FROM customers c RIGHT JOIN orders o ON ...;  -- ⚠️ prefer swap

Common Pitfalls

Pitfall	Why It Happens	Fix
Unmatched rows missing	Filter on the right table in WHERE	Move the filter to ON
NULL in unexpected columns	The left table was not matched	Expected; check the join condition
Confusing mixed joins	LEFT and RIGHT in the same query	Use only LEFT JOIN
COUNT returns 1 for unmatched	Used `COUNT(*)`	Use `COUNT(left.key)`
Aggregate includes NULL group	Unmatched rows grouped by NULL	Filter with `IS NOT NULL`
Rewriting changes result	Forgot to swap the tables	Swap both the FROM and JOIN order

Real-World Examples

1. All Orders with Customer Names

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

2. Same Query as LEFT JOIN

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

3. Orders Without Customers

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

4. Same with NOT EXISTS

SELECT o.order_id FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);

5. Right Join with Count

SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

6. Exclude Unmatched

SELECT c.name, COUNT(o.order_id)
FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NOT NULL
GROUP BY c.name;

7. Filter in ON

SELECT c.name, o.order_id
FROM customers c RIGHT JOIN orders o
  ON c.customer_id = o.customer_id AND o.total > 200;

8. Filter in WHERE Breaks Outer

SELECT c.name, o.order_id
FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 200;

9. Multiple Tables

SELECT c.name, o.order_id, p.name
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id;

10. Preferred Rewrite

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

Visual

RIGHT JOIN Behavior

┌──────────────────────────────────────────────────────────────┐
│  customers              orders                               │
│  ┌────┬────────┐        ┌─────┬──────┐                       │
│  │ id │ name   │        │ oid │ cid  │                       │
│  ├────┼────────┤        ├─────┼──────┤                       │
│  │ 1  │ Alice  │        │ 101 │ 1    │                       │
│  │ 2  │ Bob    │        │ 102 │ 1    │                       │
│  │ 3  │ Carol  │        │ 103 │ 2    │                       │
│  └────┴────────┘        │ 104 │ 9    │                       │
│                         └─────┴──────┘                       │
│                                                              │
│  RIGHT JOIN ON c.id = o.cid:                                 │
│  ┌────────┬─────┐                                            │
│  │ Alice  │ 101 │                                            │
│  │ Alice  │ 102 │                                            │
│  │ Bob    │ 103 │                                            │
│  │ NULL   │ 104 │  ← order 104 preserved, no customer        │
│  └────────┴─────┘                                            │
│                                                              │
│  Carol is excluded because she has no orders.                │
└──────────────────────────────────────────────────────────────┘

RIGHT vs LEFT Equivalence

┌──────────────────────────────────────────────────────────────┐
│  RIGHT JOIN:                                                 │
│  FROM customers c RIGHT JOIN orders o ON c.id = o.cid        │
│  └── preserves orders (the right table)                      │
│                                                              │
│  LEFT JOIN (swapped):                                        │
│  FROM orders o LEFT JOIN customers c ON o.cid = c.id         │
│  └── preserves orders (the left table)                       │
│                                                              │
│  Same result, different table order.                         │
└──────────────────────────────────────────────────────────────┘

Filter Placement

┌──────────────────────────────────────────────────────────────┐
│  FILTER IN ON:                                               │
│  RIGHT JOIN orders o ON c.id = o.cid AND o.total > 200       │
│  └── Unmatched right rows preserved with NULLs               │
│                                                              │
│  FILTER IN WHERE:                                            │
│  RIGHT JOIN orders o ON c.id = o.cid WHERE o.total > 200     │
│  └── Unmatched right rows removed (behaves like inner join)  │
└──────────────────────────────────────────────────────────────┘

Unmatched Detection

┌──────────────────────────────────────────────────────────────┐
│  RIGHT JOIN orders o ON c.id = o.cid                         │
│  WHERE c.id IS NULL                                          │
│                                                              │
│  Result: orders with no matching customer                    │
│                                                              │
│  Mirror of:                                                  │
│  LEFT JOIN orders o ON c.id = o.cid                          │
│  WHERE o.id IS NULL                                          │
│                                                              │
│  Both find unmatched rows; the direction differs.            │
└──────────────────────────────────────────────────────────────┘

Summary

Item	Value
RIGHT JOIN	All right rows + matching left rows
Unmatched right rows	Appear with NULL in left columns
Right table	Named after `RIGHT JOIN`
Equivalent to	LEFT JOIN with tables swapped
Unmatched detection	`WHERE left.key IS NULL`
COUNT with RIGHT JOIN	Use `COUNT(left.key)` for matches
Filter placement	ON preserves outer; WHERE removes
Convention	LEFT JOIN preferred
Mixed joins	Avoid LEFT + RIGHT in one query

Key takeaways:

RIGHT JOIN preserves all rows from the right table. Unmatched right rows appear with NULL in the left columns. It is the mirror of the LEFT JOIN.
A RIGHT JOIN is equivalent to a LEFT JOIN with the tables swapped. The result is identical. The choice is a matter of which table the reader sees first.
Most developers prefer LEFT JOIN. Naming the preserved table first makes the query read naturally. Rewriting a right join as a left join with swapped tables produces the same result with the more common convention.
WHERE left.key IS NULL detects unmatched right rows. This is the mirror of the LEFT JOIN pattern for detecting unmatched left rows.
COUNT(left.key) counts matches, not rows. COUNT(*) returns 1 for an unmatched right row; COUNT(left.key) returns 0.
Filter placement matters for outer joins. A condition in ON preserves unmatched rows. The same condition in WHERE removes them and turns the outer join into an inner join.
Avoid mixing LEFT and RIGHT joins in one query. The combination is confusing and the result is hard to reason about. Use LEFT JOIN throughout, or restructure with subqueries.

Remember: The RIGHT JOIN is the mirror of the LEFT JOIN. It preserves the right table’s rows and fills the left columns with NULL when there is no match. Because it is equivalent to a LEFT JOIN with the tables swapped, most developers use only LEFT JOINs and rewrite right joins by swapping the table order. The result is the same, and the query reads more consistently. Understanding the RIGHT JOIN is necessary for reading existing queries and for recognizing when a query’s structure comes from the right side of the join. The patterns for detecting unmatched rows, aggregating with outer joins, and placing filters in ON versus WHERE apply the same way as with the LEFT JOIN, with the direction reversed.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!