SQL LEFT JOIN and RIGHT JOIN Explained

An INNER JOIN only returns rows that match in both tables — but sometimes you need the opposite: every row from one table, whether or not it has a match on the other side. That's exactly what LEFT JOIN and RIGHT JOIN are for. If you've already read about SQL INNER JOIN, these two are a natural next step.

Using the familiar customers (id, name, city) and orders (id, customer_id, amount, order_date) tables, imagine a customer who has never placed an order. An inner join would silently drop that customer from the results. A left join keeps them — just with NULL in every order-related column.

SQL LEFT JOIN Explained

LEFT JOIN (also written LEFT OUTER JOIN) returns every row from the "left" table — the one listed first, right after FROM — plus matching rows from the right table. Where there's no match, the right table's columns come back as NULL.

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

Result:

name | city | order_id | amount
---------|-----------|----------|-------
Alice | Chicago | 101 | 250.00
Alice | Chicago | 104 | 75.50
Bob | Austin | 102 | 120.00
Charlie | Denver | NULL | NULL

Notice Charlie still appears even though he has no matching row in orders — his order columns are simply NULL. This is the signature behavior of a left join, and it's exactly how you'd write a query like "show me every customer, including the ones who've never ordered anything."

SQL RIGHT JOIN Explained

RIGHT JOIN is the mirror image: it keeps every row from the right-hand table, filling in NULL for unmatched rows on the left. In practice, right joins are far less common than left joins — almost anything you can write with a RIGHT JOIN can be rewritten as a LEFT JOIN by simply swapping the table order, which most developers find easier to read.

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

This produces the exact same result as the LEFT JOIN example above — only the table order and join direction changed. Because of this, many style guides recommend standardizing on LEFT JOIN everywhere and avoiding RIGHT JOIN entirely, just to keep queries consistent and easier to scan.

Filtering NULLs from an Outer Join

A common follow-up task is finding rows with no match at all — for example, "which customers have never ordered?" Add a WHERE clause checking for NULL on the joined side:

SELECT c.name, c.city
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

Result:

name | city
---------|-------
Charlie | Denver

This "left join + IS NULL" pattern is one of the most useful tricks in SQL — it's the standard way to answer "what's missing" questions, and it comes up constantly once you start working with real, imperfect data.

---