SQL for .NET developers Beginner

Joining tables

Data about one thing is often spread across tables: customers in one, orders in another. JOIN combines rows from several tables in one query. This lesson explains INNER, LEFT, RIGHT, FULL and CROSS joins with a picture of what each returns.

What each join returns (Customers joined to Orders)
  1. INNER JOINOnly customers who have orders, with those orders
  2. LEFT JOINAll customers; order columns are NULL for customers with none
  3. RIGHT JOINAll orders, even ones without a matching customer
  4. FULL OUTER JOINEverything from both sides, matched where possible
  5. CROSS JOINEvery customer paired with every order; rarely what you want

The ON clause says which rows match, usually a foreign key equal to a primary key.

The idea in plain words

A join looks at two tables and pairs up rows that belong together according to a condition, usually Orders.CustomerId = Customers.CustomerId. The kind of join decides what happens to rows that have no partner on the other side: drop them (INNER) or keep them with NULLs (LEFT, RIGHT, FULL).

Example

Table aliases (c and o) keep queries short.

SQL
-- Customers who have ordered, with each order
SELECT c.Name, o.OrderId, o.Total
FROM Customers AS c
INNER JOIN Orders AS o ON o.CustomerId = c.CustomerId;

-- All customers, including those with no orders
SELECT c.Name, o.OrderId
FROM Customers AS c
LEFT JOIN Orders AS o ON o.CustomerId = c.CustomerId;

-- Customers who have never ordered
SELECT c.Name
FROM Customers AS c
LEFT JOIN Orders AS o ON o.CustomerId = c.CustomerId
WHERE o.OrderId IS NULL;

-- Three tables: order lines with product names
SELECT o.OrderId, p.Name, l.Quantity
FROM Orders AS o
JOIN OrderLines AS l ON l.OrderId = o.OrderId
JOIN Products  AS p ON p.ProductId = l.ProductId;

Common mistakes

  • Forgetting the ON condition or using the wrong columns, which multiplies rows.
  • Filtering the right-hand table of a LEFT JOIN in WHERE, which silently turns it into an INNER JOIN. Put that filter in the ON clause instead.
  • Joining many large tables and selecting every column when only a few are needed.
  • Not indexing foreign key columns used in joins.

Interview question

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table and the matching rows from the right table; when there is no match, the right table's columns are NULL. For example, an INNER JOIN of customers and orders omits customers without orders, while a LEFT JOIN includes them.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.