Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 39 ยท Joins, CTEs and Window Functions

Inner and Outer Joins in SQL Server

Combine tables with INNER, LEFT, RIGHT and FULL OUTER JOIN in T-SQL, learn ON versus WHERE for outer joins, join on multiple keys and find unmatched rows.

A well-designed database splits data across tables: customers in one, orders in another, products in a third. A join puts rows from two tables side by side, matching them on related columns, usually a foreign key pointing at a primary key. Joins are the heart of relational querying, and most real queries use several. This lesson explains inner and outer joins, the difference between ON and WHERE for outer joins, joining on multiple columns, and how to find rows with no match.

The shop database has customers, orders, order_items, products and categories. orders.customer_id points to customers.id.

flowchart LR
  C["customers<br/>id"] -- "1 : many" --> O["orders<br/>customer_id"]
  O -- "1 : many" --> I["order_items<br/>order_id, product_id"]
  P["products<br/>id"] -- "1 : many" --> I
  G["categories<br/>id"] -- "1 : many" --> P

The relationships in the shop database. Joins follow these arrows.

INNER JOIN

An inner join returns only rows that have a match in both tables. INNER is optional: JOIN alone means inner join.

SQL
SELECT c.first_name, c.last_name, o.id AS order_id, o.status
FROM dbo.customers AS c
INNER JOIN dbo.orders AS o
        ON o.customer_id = c.id;

Read it as: for every customer row, find the order rows whose customer_id equals the customer's id. Customers without orders disappear from the result, and a customer with five orders appears five times.

SQL Customers and their orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Always give tables short aliases and qualify every column (c.id, o.id). An unqualified column that exists in both tables raises "Ambiguous column name".

LEFT OUTER JOIN

A left join keeps every row of the left table, and fills the right table's columns with NULL when there is no match. OUTER is optional (LEFT JOIN).

SQL All customers, even those without orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Note COUNT(o.id) rather than COUNT(*): for a customer with no orders, COUNT(*) would report 1 (the single null-extended row), while COUNT(o.id) correctly reports 0.

RIGHT and FULL OUTER JOIN

RIGHT JOIN keeps every row of the right table; it is just a LEFT JOIN with the tables swapped, so most developers write left joins only and reorder the tables. FULL OUTER JOIN keeps all rows from both sides, with NULLs wherever either side lacks a match. It is useful for reconciling two lists: what exists in one but not the other.

SQL
SELECT COALESCE(a.sku, b.sku) AS sku,
       a.qty AS warehouse_qty,
       b.qty AS store_qty
FROM dbo.warehouse_stock AS a
FULL OUTER JOIN dbo.store_stock AS b ON b.sku = a.sku
WHERE a.sku IS NULL OR b.sku IS NULL;    -- items present on only one side

SQLite (the live editor's engine) supports FULL JOIN in recent versions, but we show the typical reconciliation as read-only to keep the focus on T-SQL.

Finding rows with no match (anti-join)

"Which customers never placed an order?" Use a left join and keep the rows where the right side is NULL:

SQL Customers without orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Test the right table's primary key (or any non-null column) with IS NULL; testing a nullable column could confuse a real NULL with a missing match. NOT EXISTS expresses the same thing and often performs identically.

ON versus WHERE in outer joins

For inner joins, a condition in ON or in WHERE gives the same result. For outer joins it does not:

  • A condition in ON decides which rows match. Unmatched left rows are still kept.
  • A condition in WHERE filters the final result, and can remove the null-extended rows, turning your left join into an inner join.
SQL
-- Keeps every customer; only delivered orders are matched:
SELECT c.id, o.id
FROM dbo.customers AS c
LEFT JOIN dbo.orders AS o ON o.customer_id = c.id AND o.status = N'delivered';

-- Drops customers without delivered orders (the NULL rows fail the WHERE):
SELECT c.id, o.id
FROM dbo.customers AS c
LEFT JOIN dbo.orders AS o ON o.customer_id = c.id
WHERE o.status = N'delivered';
SQL Condition in ON versus WHERE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Joining several tables and multiple keys

Chain joins one after another; each ON relates the new table to something already in the query:

SQL
SELECT c.last_name, o.id AS order_id, p.name AS product, i.quantity
FROM dbo.customers   AS c
JOIN dbo.orders      AS o ON o.customer_id = c.id
JOIN dbo.order_items AS i ON i.order_id   = o.id
JOIN dbo.products    AS p ON p.id         = i.product_id
WHERE o.status = N'delivered';

When a relationship uses a composite key, put every column in ON with AND: ON a.country = b.country AND a.zip = b.zip.

SQL Four tables joined ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Duplicates and wrong totals

A join multiplies rows when the relationship is one-to-many. If you join orders to order_items and then sum a column that belongs to orders (such as shipping), each order's shipping is counted once per item. Aggregate each side separately in a subquery or CTE, then join the results. The T-SQL join hints (LOOP, HASH, MERGE) exist but should be left to the optimizer in normal work.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which join returns only rows with a match in both tables?

    INNER JOIN (or just JOIN) keeps only matching pairs.
  2. 2. How do you list customers who have no orders?

    A left join keeps all customers; unmatched ones have NULL order columns, which the WHERE selects.
  3. 3. What happens if you add WHERE o.status = 'delivered' to a LEFT JOIN?

    Null-extended rows fail the WHERE condition. Put the condition in ON to keep unmatched left rows.
  4. 4. Why use COUNT(o.id) rather than COUNT(*) after a LEFT JOIN?

    COUNT(o.id) skips NULLs, so customers with no orders correctly return 0.

Summary

  • INNER JOIN keeps matches; LEFT/RIGHT keep all rows from one side; FULL keeps everything.
  • Alias tables and qualify columns to avoid ambiguity.
  • Use LEFT JOIN ... WHERE right.key IS NULL (or NOT EXISTS) to find missing matches.
  • In outer joins, conditions on the optional table belong in ON.
  • One-to-many joins multiply rows; aggregate before joining when totals matter.

Next: CROSS JOIN, Self Joins and APPLY