PrismitaLEARN · PRACTICE · GROW

What is the difference between an INNER JOIN and a LEFT JOIN?

SQL Server Joins Basic Fresher1–2 Yrs SQL Query 2 min read

Quick Answer

An INNER JOIN returns only rows that have a match in both tables. A LEFT JOIN (left outer join) returns every row from the left table, plus matching columns from the right table where they exist and NULLs where they don't. Use a LEFT JOIN when you want “all customers, and their orders if any”.

An inner join only keeps rows that match on both sides. A left join keeps every row from the left table and fills in nulls when there's no match on the right — so “all customers and their orders if they have any.” The classic trap is filtering the right table in the WHERE clause of a left join: as soon as you write WHERE o.Status = 1, the null rows fail that test and you've quietly turned it back into an inner join. If you want to filter the right side while keeping the outer rows, the condition goes in the ON clause. And LEFT JOIN ... WHERE o.OrderId IS NULL is how you find customers with no orders.

Given Customers and Orders:

  • Customers INNER JOIN Orders — customers who have placed at least one order; customers with no orders disappear.
  • Customers LEFT JOIN Orders — every customer; those with no orders show NULL for the order columns.
  • RIGHT JOIN — the mirror image (all rows from the right table). Usually rewritten as a LEFT JOIN for readability.
  • FULL OUTER JOIN — all rows from both sides, NULLs where either side is missing.
  • CROSS JOIN — every combination (Cartesian product), no ON.

Gotchas interviewers test:

  • Putting a filter on the right table in the WHERE clause of a LEFT JOIN (WHERE o.Status = 1) silently turns it back into an inner join, because NULL = 1 is unknown. Put that condition in the ON clause instead.
  • LEFT JOIN ... WHERE right.Key IS NULL is the standard “find rows with no match” (anti-join) pattern.
  • A missing join predicate produces an accidental cross join and an explosion of rows.

SQL — the WHERE-on-outer-table trap 
-- All customers, with their 2026 orders if any (stays a LEFT JOIN)
SELECT c.Name, o.OrderId, o.Total
FROM   dbo.Customers c
LEFT JOIN dbo.Orders o
       ON o.CustomerId = c.CustomerId
      AND o.OrderDate >= '2026-01-01';   -- filter in ON

-- Customers who have never ordered (anti-join)
SELECT c.Name
FROM   dbo.Customers c
LEFT JOIN dbo.Orders o ON o.CustomerId = c.CustomerId
WHERE  o.OrderId IS NULL;
All SQL Server questions More on Joins