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;