LEFT JOIN vs RIGHT JOIN in SQL: What's the Difference?

The difference is which table keeps all of its rows: LEFT JOIN returns every row from the left (first) table plus matching rows from the right, while RIGHT JOIN returns every row from the right (second) table plus matching rows from the left. Unmatched rows get NULL in the other table's columns.

Which Table Is the Left Table and Which Is the Right?

The left table is the one written before the JOIN keyword and the right table is the one written after it; LEFT JOIN keeps every row of the first, RIGHT JOIN every row of the second. The order of the columns in the ON clause makes no difference, and matching logic and NULL filling are otherwise identical, so the choice is purely about which side you want kept in full.

	LEFT JOIN	RIGHT JOIN
Keeps all rows from	Left (first) table	Right (second) table
NULLs appear in	Right table's columns	Left table's columns
Full name	LEFT OUTER JOIN	RIGHT OUTER JOIN
Usage in practice	Very common	Rare (usually rewritten as LEFT)

Need all rows from both sides? That's a FULL OUTER JOIN. Only matching rows? That's an INNER JOIN.

What Does LEFT JOIN (LEFT OUTER JOIN) Return?

A LEFT JOIN returns all rows from the left table plus the matching rows from the right table, with NULL in the right table's columns wherever there is no match. LEFT OUTER JOIN is the same join (the OUTER keyword is optional), and a left row that matches several right rows appears once per match.

-- Get ALL customers, including those without orders
SELECT
    customers.name,
    customers.email,
    orders.order_id,
    orders.total
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Result includes every customer. Customers without orders will have NULL in the order_id and total columns.

What Does RIGHT JOIN (RIGHT OUTER JOIN) Return?

A RIGHT JOIN returns all rows from the right table plus the matching rows from the left table, with NULL in the left table's columns wherever there is no match. RIGHT OUTER JOIN means the same thing; it is used far less than LEFT JOIN because the same result is usually written as a LEFT JOIN with the tables swapped.

-- Get ALL orders, including orphaned orders without customer info
SELECT
    customers.name,
    orders.order_id,
    orders.total
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

Result includes every order. Orders whose customer_id has no match in customers will have NULL in the name column.

Can Every RIGHT JOIN Be Rewritten as a LEFT JOIN?

Yes. Swapping the order of the two tables and changing RIGHT to LEFT produces an identical result set — same rows, same NULL pattern. The join condition doesn't even need to change, since a = b equals b = a. That symmetry is why some teams ban RIGHT JOIN entirely: anything it does, a reordered LEFT JOIN does more readably.

-- RIGHT JOIN version: keep all orders
SELECT * FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- Equivalent LEFT JOIN: swap the tables, keep all orders
SELECT * FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;

-- Both return every order, with NULL customer columns
-- for orders that have no matching customer.

Why Does LEFT JOIN Dominate in Practice?

Because queries read top-to-bottom, left-to-right: you start FROM your main table and progressively attach lookups to it. LEFT JOIN keeps that mental model — "my base table, plus whatever matches." RIGHT JOIN forces readers to reason backwards from the end of the clause, and it composes badly when chaining three or more joins, so style guides and ORMs overwhelmingly emit LEFT JOIN.

Finding Non-Matching Records

LEFT JOIN the second table, then keep only rows where a right-table column IS NULL; what remains are the left rows with no match. Test a column that can never be NULL in a real match, such as the right table's primary key or join key, or rows that matched but hold a NULL value will slip through.

Customers with No Orders

SELECT
    customers.customer_id,
    customers.name,
    customers.email,
    customers.signup_date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

Products Never Sold

SELECT
    products.product_id,
    products.product_name,
    products.price
FROM products
LEFT JOIN order_items ON products.product_id = order_items.product_id
WHERE order_items.order_id IS NULL;

Practical Use Cases

Reach for LEFT JOIN whenever a report must list every row of its base table, including customers, users, or products with zero activity. Wrap SUM of right-table columns in COALESCE so those rows show 0 instead of NULL; COUNT(column) already returns 0 for them.

Customer Engagement Report

SELECT
    c.customer_id,
    c.name,
    c.signup_date,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.total), 0) as lifetime_value,
    CASE
        WHEN COUNT(o.order_id) = 0 THEN 'Never Purchased'
        WHEN COUNT(o.order_id) < 3 THEN 'Low Engagement'
        ELSE 'Active Customer'
    END as customer_status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name, c.signup_date
ORDER BY order_count DESC;

Email List with Purchase History

SELECT
    u.email,
    u.first_name,
    u.last_name,
    MAX(o.order_date) as last_purchase_date,
    DATEDIFF(CURDATE(), MAX(o.order_date)) as days_since_purchase
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.email_opt_in = true
GROUP BY u.email, u.first_name, u.last_name;

Complete Inventory Report

SELECT
    p.product_id,
    p.product_name,
    p.stock_quantity,
    COALESCE(SUM(oi.quantity), 0) as total_sold,
    COALESCE(SUM(oi.quantity * oi.price), 0) as revenue
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.stock_quantity
ORDER BY revenue DESC;