LEFT JOIN and RIGHT JOIN in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this INNER JOIN, NULL Semantics and IS NULL
Builds toward FULL OUTER JOIN, Joining Multiple Tables, NULL Handling in Joins and Aggregates, generate_series() for Sequences and Date Spines
What are LEFT JOIN and RIGHT JOIN in SQL?
LEFT JOIN keeps every row from the left table, whether or not it finds a match in the right table.
You're building a customer report and you need to see all customers — including the ones who haven't placed any orders yet. INNER JOIN would silently drop customers with no orders, because there's nothing to match them against in the orders table. LEFT JOIN keeps every customer. Where there's no matching order, the order columns come back as NULL.
That's the core distinction: INNER JOIN only returns rows with matches on both sides; LEFT JOIN returns everything from the left side plus any matches from the right.
How do you write a LEFT JOIN in SQL?
Here's what it looks like. All customers, with their orders where they exist:
SELECT c.name, o.id AS order_id, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_idCustomers with orders get rows showing both customer and order data. Customers with no orders still appear — one row per customer, with order_id and total_amount as NULL.
How do you find rows with no match using LEFT JOIN?
The NULL on the right side becomes a tool. You can use WHERE right_table.id IS NULL to find exactly the rows with no match:
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULLThis returns customers with no orders at all. The join finds the unmatched rows; the WHERE filter keeps only those. It's the standard pattern for gap analysis — finding records in one table with nothing corresponding in another.
What is the difference between LEFT JOIN and RIGHT JOIN?
RIGHT JOIN is the same logic flipped: every row from the right table is kept, with NULLs filling the left side where there's no match. In practice, most analysts flip the table order and use LEFT JOIN instead of writing RIGHT JOIN — it reads more naturally to keep the main table on the left.
SELECT cat.name AS category_name, p.name AS product_name FROM products p RIGHT JOIN categories cat ON p.category_id = cat.id
Every category appears, even ones with no products. Products with no category are excluded because categories is the right-side table being preserved.
Which table does a LEFT JOIN actually keep?
The one thing that trips people up: confusing which side is being preserved.
In FROM A LEFT JOIN B, all rows in A survive — A is the left table. In FROM A RIGHT JOIN B, all rows in B survive — B is the right table. When a result is missing rows you expected, check which table is on the preserved side and whether it should be.
You write: FROM customers c LEFT JOIN orders o ON c.id = o.customer_id. Which rows are guaranteed to appear?
Practice LEFT JOIN and RIGHT JOIN in SQL
Brightlane's customer success team needs a list of every registered customer alongside any orders they have placed. Customers who have not yet placed any orders must still appear in the list.
Write a query to return each customer's name, order ID, and order total. Order columns will be missing for customers who have placed no orders.
Assumptions:
- The
customerstable contains every customer Brightlane has on file. - The
orderstable contains every order;customer_idon each order points to a customer. - Some customers have placed no orders. Those customers must still appear in the result, with the order columns missing.
Output:
- One row per customer-order pair, plus one row per customer with no orders, with columns
name,order_id, andtotal_amount. Order columns will be missing for customers with no orders.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solutionStation Zero, our free browser SQL game, teaches this concept inside a story. No signup.
hunt an anti-join in Station Zero9 LEFT JOIN and RIGHT JOIN practice problems
Write a query to return each customer's name, order ID, and order total. Order columns will be missing for customers who have placed no orders.
Write a query to return the user ID and conversion amount for every user, including users who have never converted (their conversion amount will be NULL).
Write a query to return the category name and product name for every category. Empty categories should still appear, with the product column missing.
Write a query to return the names of every customer with no order history.
Write a query to return each Canadian customer's name alongside their order ID and order total. Canadian customers who have not yet placed any orders should still appear, with the order columns missing.
Write a query to return the user ID and account plan for every user with no recorded sessions.
Write a query to return the product name for every product that has never appeared on any order line.
Write a query to return the category name for every empty category.
Write a query to return every free-plan user alongside their conversion amounts. Users who have never converted must still appear, with the conversion column missing.
Start learning to practice all 9 LEFT JOIN and RIGHT JOIN problems, with instant grading and mastery tracking.
Common questions about LEFT JOIN and RIGHT JOIN
Why does my LEFT JOIN return more rows than the left table?
Because a left row matching several right rows produces one output row per match. A LEFT JOIN guarantees that every left row appears at least once, not exactly once, so a customer with five orders arrives as five rows.
Can a LEFT JOIN ever return fewer rows than the left table?
Not from the join itself, which keeps every left row whether or not it matches. A filter can still remove them afterwards, and a WHERE clause testing a right-side column is the usual way that happens by accident.
Is RIGHT JOIN ever the better choice?
Rarely. It does the same work as a LEFT JOIN with the tables swapped, and most people read a query faster when the table being preserved is the one named first. Reach for it when rewriting the FROM clause would make the query harder to follow.
