Joins and Subqueries

Real-world databases store data across multiple related tables. This lesson covers how to combine data using joins, subqueries, common table expressions (CTEs), APPLY, and PIVOT.

---

Setting Up Example Tables

CREATE TABLE Authors (
    AuthorId  INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50)  NOT NULL,
    LastName  NVARCHAR(50)  NOT NULL,
    Country   NVARCHAR(50)
);

CREATE TABLE Books (
    BookId    INT IDENTITY(1,1) PRIMARY KEY,
    Title     NVARCHAR(200) NOT NULL,
    AuthorId  INT           NOT NULL REFERENCES Authors(AuthorId),
    Genre     NVARCHAR(50),
    Price     DECIMAL(8,2)  NOT NULL,
    Published DATE
);

CREATE TABLE Orders (
    OrderId   INT IDENTITY(1,1) PRIMARY KEY,
    BookId    INT           NOT NULL REFERENCES Books(BookId),
    Quantity  INT           NOT NULL,
    OrderDate DATE          NOT NULL DEFAULT GETDATE()
);

---

INNER JOIN

Returns rows that have matching values in both tables:

SELECT b.Title, a.FirstName, a.LastName, b.Price
FROM Books b
INNER JOIN Authors a ON b.AuthorId = a.AuthorId;

Only books with a matching author (and vice versa) appear in the result.

---

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table plus matches from the right:

SELECT a.FirstName, a.LastName, b.Title
FROM Authors a
LEFT JOIN Books b ON a.AuthorId = b.AuthorId;

Authors without books will appear with NULL values for the book columns.

---

RIGHT JOIN and FULL OUTER JOIN

-- RIGHT JOIN — all rows from the right table
SELECT a.FirstName, b.Title
FROM Authors a
RIGHT JOIN Books b ON a.AuthorId = b.AuthorId;

-- FULL OUTER JOIN — all rows from both tables
SELECT a.FirstName, b.Title
FROM Authors a
FULL OUTER JOIN Books b ON a.AuthorId = b.AuthorId;

---

Join Types Summary

Join Type	Returns
INNER JOIN	Matching rows from both tables
LEFT JOIN	All left rows + matching right rows (NULLs where no match)
RIGHT JOIN	All right rows + matching left rows (NULLs where no match)
FULL OUTER JOIN	All rows from both tables (NULLs where no match on either side)
CROSS JOIN	Cartesian product (every combination of rows)

---

Multi-Table Joins

SELECT
    a.FirstName + ' ' + a.LastName AS Author,
    b.Title,
    o.Quantity,
    o.OrderDate
FROM Orders o
INNER JOIN Books b ON o.BookId = b.BookId
INNER JOIN Authors a ON b.AuthorId = a.AuthorId
ORDER BY o.OrderDate DESC;

---

Self Joins

A table joined to itself — useful for hierarchical data:

-- Employees reporting to a manager
CREATE TABLE Employees (
    EmpId     INT PRIMARY KEY,
    Name      NVARCHAR(50),
    ManagerId INT REFERENCES Employees(EmpId)
);