Home / SQL / RIGHT JOIN

RIGHT JOIN

Overview

The RIGHT JOIN (or RIGHT OUTER JOIN) returns all rows from the right table and the matched rows from the left table. If there is no match, NULL values are returned for columns from the left table.

RIGHT JOIN is the mirror of LEFT JOIN - it preserves all rows from the right table instead of the left table.

Basic Syntax

RIGHT JOIN Syntax

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Visual Representation

RIGHT JOIN Diagram

Table A (LEFT)    Table B (RIGHT)
┌─────┐         ┌─────┐
│  1  │         │  3  │
│  2  │─────────│  2  │  Returns: 2, 3, 4, 5 (all B)
│  3  │         │  4  │  (2 and 3 have matches in A)
│  4  │         │  5  │  (4 and 5 have NULLs for A columns)
└─────┘         └─────┘

Result: All rows from right table + matching rows from left
        Non-matching rows have NULL in left table columns

Examples

Example 1: Basic RIGHT JOIN

All orders with customer info

SELECT 
    c.name AS customer_name,
    o.order_date,
    o.total_amount
FROM Customers c
RIGHT JOIN Orders o 
ON c.customer_id = o.customer_id;

Returns: All orders, including those without customer records (NULL in customer columns).

Example 2: Find Orphaned Orders

Orders without customers

SELECT 
    o.order_id,
    o.order_date,
    o.total_amount
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

Returns: Orders that don't have a matching customer (orphaned orders).

RIGHT JOIN vs LEFT JOIN

RIGHT JOIN and LEFT JOIN are mirrors - you can achieve the same result by swapping table order:

Equivalent Queries

-- RIGHT JOIN
SELECT *
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id;

-- Equivalent LEFT JOIN (swapped tables)
SELECT *
FROM Orders o
LEFT JOIN Customers c ON o.customer_id = c.customer_id;

-- Both return the same results!

Why RIGHT JOIN is Less Common

RIGHT JOIN is less commonly used because:

LEFT JOIN with swapped tables achieves the same result
Reading from left-to-right makes LEFT JOIN more intuitive
Most developers prefer consistent LEFT JOIN usage

When to Use RIGHT JOIN

When you need all rows from the right table
To find orphaned records in the right table
When the right table is the primary focus
For specific query structure preferences

Best Practices

Consider LEFT JOIN: Often more readable when you swap table order
Understand NULL behavior: Unmatched rows have NULL in left table columns
Index join columns: Especially the left table's join column
Document purpose: Make it clear why RIGHT JOIN is needed

Next Steps

Learn about FULL JOIN which includes all rows from both tables, or review LEFT JOIN which is more commonly used.

← Left Join Full Join →