LEFT, RIGHT & FULL JOIN

An INNER JOIN only returns rows where a matching record exists in both tables — it is a filter as much as it is a join. But some of the most important business questions require the opposite: show me everything on one side even when there is nothing matching on the other. Which customers have never placed an order? Which products have never been reviewed? Which orders have no line items? These questions cannot be answered with INNER JOIN because the rows you are looking for are precisely the ones that have no match. Outer joins — LEFT, RIGHT, and FULL — extend the join by preserving unmatched rows from one or both tables, filling the missing side with NULL. Understanding when to use each, and how to correctly filter for the unmatched rows, is essential for writing complete and honest queries.

LEFT JOIN

A LEFT JOIN returns every row from the left table (the one named before the JOIN keyword) and any matching rows from the right table. When no match exists in the right table, SQL Server still includes the left row and fills every column from the right table with NULL. The left table is never filtered — you always get all of its rows. LEFT JOIN is the correct tool whenever the question starts with "give me all [left table rows] and whatever [right table] data exists for them". It is by far the most commonly used outer join in practice because most analytical questions start from one primary entity — users, products, orders — and optionally bring in related data.

-- LEFT JOIN — all users, with their order count (including users with no orders)
USE DataplexaStore;

SELECT
    u.UserID,
    u.FullName,
    u.Country,
    COUNT(o.OrderID)        AS order_count,     -- 0 for users with no orders
    SUM(o.TotalAmount)      AS total_spent       -- NULL for users with no orders
FROM       Users      u
LEFT JOIN  Orders     o  ON u.UserID = o.UserID
GROUP BY   u.UserID, u.FullName, u.Country
ORDER BY   order_count DESC, u.FullName;

• Users 5 through 10 have never placed an order — a LEFT JOIN includes them with order_count = 0 and total_spent = NULL; an INNER JOIN would have silently excluded all six of them
• COUNT(o.OrderID) returns 0 for unmatched users rather than NULL because COUNT of a NULL column returns 0 — but SUM(o.TotalAmount) returns NULL because SUM of a set containing only NULLs is NULL, not zero
• Alice (UserID 1) does not appear because her rows were deleted in the earlier Delete Data lesson — the dataset reflects the live state of the DataplexaStore tables

Filtering for Unmatched Rows — the Anti-Join Pattern

The most powerful use of LEFT JOIN is finding rows that have no match at all — the anti-join pattern. After a LEFT JOIN, any row with NULL in a right-table column has no match. Adding WHERE right_table.column IS NULL filters the result to only the unmatched rows. This pattern answers questions like "which users have never ordered", "which products have never been reviewed", and "which orders have no line items". It is cleaner and often faster than equivalent NOT IN or NOT EXISTS subqueries for this purpose, and it makes the intent immediately visible in the query structure.

-- Anti-join pattern — find rows with no match in the right table

-- Users who have never placed an order
SELECT
    u.UserID,
    u.FullName,
    u.Country,
    u.MembershipTier
FROM      Users   u
LEFT JOIN Orders  o  ON u.UserID = o.UserID
WHERE     o.OrderID IS NULL          -- NULL here means no order row matched
ORDER BY  u.FullName;

-- Products that have never been reviewed
SELECT
    p.ProductID,
    p.ProductName,
    p.Category,
    p.Price
FROM      Products  p
LEFT JOIN Reviews   r  ON p.ProductID = r.ProductID
WHERE     r.ReviewID IS NULL         -- NULL here means no review row matched
ORDER BY  p.Category, p.ProductName;

-- Products that have never been ordered
SELECT
    p.ProductID,
    p.ProductName,
    p.Category
FROM      Products    p
LEFT JOIN OrderItems  oi ON p.ProductID = oi.ProductID
WHERE     oi.OrderItemID IS NULL
ORDER BY  p.Category;

• Six users have never ordered — representing a significant segment of the customer base; a targeted email campaign or discount offer to these users would be a direct business action driven by this query
• The anti-join column in WHERE must be from the right table and should be a column that is NOT NULL in the actual data — using the primary key (OrderID, ReviewID, OrderItemID) is the safest choice because primary keys are always non-NULL, so NULL in that column definitively means no match
• Webcam HD appears in both "never reviewed" and "never ordered" — it is a product that has neither sold nor attracted any feedback, which is actionable inventory intelligence

RIGHT JOIN

A RIGHT JOIN is the mirror of LEFT JOIN — it returns every row from the right table and any matching rows from the left table, filling the left side with NULL when no match exists. In practice RIGHT JOIN is rarely used because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Most developers prefer LEFT JOIN consistently and simply change which table is listed first rather than switch to RIGHT JOIN, because mixing both in a codebase reduces readability. RIGHT JOIN is included here for completeness and because you will encounter it in existing code, but there is no scenario where RIGHT JOIN achieves something LEFT JOIN cannot.

-- RIGHT JOIN — all products with their review data (products with no reviews get NULLs)
-- Equivalent to the LEFT JOIN anti-join above but with tables swapped

SELECT
    p.ProductID,
    p.ProductName,
    r.ReviewID,
    r.Rating,
    r.ReviewText
FROM      Reviews   r
RIGHT JOIN Products p  ON r.ProductID = p.ProductID   -- Products is now the preserved table
ORDER BY  p.ProductID;

-- The exact same result using LEFT JOIN (preferred form)
SELECT
    p.ProductID,
    p.ProductName,
    r.ReviewID,
    r.Rating,
    r.ReviewText
FROM      Products  p
LEFT JOIN Reviews   r  ON p.ProductID = r.ProductID   -- Products still preserved, cleaner
ORDER BY  p.ProductID;

• Both queries produce identical results — the RIGHT JOIN and LEFT JOIN versions are semantically equivalent, the only difference is which table is listed on which side of the JOIN keyword
• Products 3, 4, 5, 6, 7, and 9 have no reviews — their ReviewID, Rating, and ReviewText are all NULL, confirming the outer join preserved them with no matching review row
• Desk Lamp (ProductID 5) was reviewed (ReviewID 4, Rating 3) in the original seed data but that review was deleted in the Delete Data lesson — it now correctly shows NULL

FULL JOIN

A FULL JOIN (also called FULL OUTER JOIN) preserves all rows from both tables. Every row from the left table appears in the result, and every row from the right table appears in the result. Where a match exists the columns from both sides are populated. Where no match exists on either side, the missing side is filled with NULL. FULL JOIN is used when you genuinely need the complete picture from both tables regardless of matching — the classic use case is comparing two datasets to find what is in one but not the other, or finding discrepancies between a source and a target during data reconciliation. It is relatively rare in day-to-day application queries but essential for data quality and migration work.

-- FULL JOIN — find any mismatch between Products and OrderItems
-- Shows products never ordered AND order items referencing missing products (data quality)

SELECT
    p.ProductID         AS product_id,
    p.ProductName,
    oi.OrderItemID,
    oi.ProductID        AS oi_product_id,
    oi.Quantity
FROM      Products    p
FULL JOIN OrderItems  oi ON p.ProductID = oi.ProductID
ORDER BY  COALESCE(p.ProductID, oi.ProductID);

-- Isolate the mismatches from both directions
SELECT
    COALESCE(p.ProductID, oi.ProductID)     AS product_id,
    p.ProductName,
    oi.OrderItemID,
    CASE
        WHEN p.ProductID  IS NULL THEN 'OrderItem references missing Product'
        WHEN oi.ProductID IS NULL THEN 'Product has no OrderItems'
    END                                     AS mismatch_type
FROM      Products    p
FULL JOIN OrderItems  oi ON p.ProductID = oi.ProductID
WHERE     p.ProductID IS NULL OR oi.ProductID IS NULL
ORDER BY  mismatch_type, product_id;

• No orphaned OrderItems appear (no rows with "OrderItem references missing Product") — the foreign key constraint on OrderItems has done its job and prevented any order line from referencing a non-existent product
• COALESCE(p.ProductID, oi.ProductID) returns the first non-NULL value — when Products has no match oi.ProductID is used, when OrderItems has no match p.ProductID is used, when both match either value works since they are identical
• FULL JOIN is particularly valuable during data migrations where foreign key constraints may not yet be in place — it surfaces orphaned records from both sides simultaneously in a single scan

Combining Multiple Outer Joins

Real queries often need to outer join more than two tables. Each additional LEFT JOIN preserves all rows from the tables already in the result and optionally brings in data from the new table. The key rule is that once a row comes in as NULL-padded from a prior outer join, subsequent inner joins on that NULL row will eliminate it — which can silently undo the work of the outer join. When chaining multiple joins, use LEFT JOIN consistently for all optional tables; switching to INNER JOIN on a table downstream of a LEFT JOIN filters out the NULL-padded rows from the outer join, often producing unexpected results.

-- Multiple LEFT JOINs — users with their orders and reviews (all users preserved)

SELECT
    u.UserID,
    u.FullName,
    COUNT(DISTINCT o.OrderID)   AS orders_placed,
    COUNT(DISTINCT r.ReviewID)  AS reviews_written,
    ISNULL(SUM(o.TotalAmount), 0)
                                AS total_spent,
    MAX(r.Rating)               AS highest_rating_given
FROM         Users    u
LEFT JOIN    Orders   o  ON u.UserID = o.UserID
LEFT JOIN    Reviews  r  ON u.UserID = r.UserID
GROUP BY     u.UserID, u.FullName
ORDER BY     total_spent DESC, u.FullName;

• All nine users appear — both LEFT JOINs preserve the Users rows regardless of whether matching Orders or Reviews rows exist
• ISNULL(SUM(o.TotalAmount), 0) converts NULL total_spent to 0 for users with no orders — SUM of a NULL set returns NULL, not zero, so the ISNULL substitution makes the output cleaner and prevents NULL propagation in downstream calculations
• COUNT(DISTINCT o.OrderID) correctly returns 0 for users with no orders — COUNT of a NULL column returns 0, making it naturally zero-friendly without needing ISNULL

Summary Table

Practice Questions

Quiz

Next up - Subqueries - Write queries inside queries to filter, calculate, and build results that a single SELECT cannot express on its own.