Joins in T-SQL

Real data lives in many related tables. You will learn how tables relate, combine them with INNER and OUTER JOINs, nest queries inside queries, and shape results with CASE.

Updated

Database Relationships

  1. Database Relationships: an overview (Apprentice): The three kinds of relationship between tables (one-to-many, one-to-one and many-to-many) and how a diagram shows each.
  2. One-to-Many (Apprentice): How a one-to-many relationship links many child rows to one parent row through a foreign key, like a customer and their orders.
  3. One-to-One (Apprentice): How a one-to-one relationship uses the child table's primary key as its foreign key, and why you would move rarely used data into its own table.
  4. Many-to-Many (Journeyman): Why two tables can't link many-to-many directly, and how an associative (junction) table between them turns it into two one-to-many relationships.

Joining Tables in T-SQL

  1. Joining Tables in T-SQL: an overview (Apprentice): A join puts related rows from two tables side by side, matched on their key columns. An inner join keeps only matches; an outer join also keeps the rows with no match.
  2. INNER JOIN (Journeyman): INNER JOIN combines two tables on a matching column, keeping only rows that match. Table aliases keep it short, and the joined columns can be filtered, grouped and sorted like any others.
  3. OUTER JOIN (Journeyman): LEFT JOIN keeps every row of the first table, matched or not; RIGHT JOIN keeps every row of the second; FULL OUTER JOIN keeps both. Rows with no match get NULL, which also lets you find what's missing.
  4. Joining Multiple Tables (Journeyman): Joins chain: each JOIN adds one more table, linked by its key to a table already in the query. A many-to-many link goes through its associative table, and a table can even join to itself.

Sub-Queries in T-SQL

  1. Types of Sub-Queries (Journeyman): A sub-query is a query inside another query, in parentheses. An uncorrelated one runs once on its own; a correlated one uses the outer query's row and runs once per row.
  2. Where to Use Sub-Queries (Journeyman): A sub-query can stand in for a value, a list or a table. Use it in WHERE with =, IN, NOT IN or EXISTS, as a column in the SELECT, or as a table in the FROM.

CASE / WHEN in T-SQL

  1. CASE / WHEN in T-SQL (Journeyman): CASE picks a value by conditions, like if and else if. Use it to label rows, to count only the rows that match with SUM(CASE ...), and to sort in a custom order.

Exercise: Joins

  1. Exercise: Joins (Journeyman): Twelve questions about Northwind that each need more than one table, answered with inner and outer joins, a self join, sub-queries and CASE, each with the result to expect.