Why Joins?

Why Joins?

Real applications store data in multiple normalised tables. A JOIN combines rows from two or more tables based on a related column (usually a FK-PK relationship), allowing you to retrieve data that spans multiple tables in a single query.

INNER JOIN

Returns only rows where the join condition matches in both tables (rows with no match are excluded).

-- Get movie title WITH its rating name:
SELECT m.title, r.ratingName
FROM   Movie m
INNER JOIN Rating r ON m.ratingCode = r.ratingCode;

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table. If there is no match on the right, NULL is returned for right table columns.

-- Find movies with NO cast members yet:
SELECT m.title, c.actorNo
FROM   Movie m
LEFT JOIN Casting c ON m.movieNo = c.movieNo
WHERE c.actorNo IS NULL;

Multi-Table JOIN

-- Get actor names and movie titles for all castings:
SELECT a.firstName, a.surname, m.title
FROM   Casting c
INNER JOIN Actor  a ON c.actorNo = a.actorNo
INNER JOIN Movie  m ON c.movieNo = m.movieNo
ORDER BY a.surname, m.title;

GROUP BY with Aggregates

-- Count movies per rating code:
SELECT ratingCode, COUNT(*) AS Total
FROM   Movie
GROUP BY ratingCode
ORDER BY Total DESC;

-- Average runtime per year (only years with avg > 100):
SELECT relyear,
       COUNT(*)          AS MovieCount,
       ROUND(AVG(runtime), 1) AS AvgRuntime
FROM   Movie
GROUP BY relyear
HAVING AVG(runtime) > 100
ORDER BY relyear;

Subqueries

-- Find movies with runtime above the average:
SELECT title, runtime
FROM   Movie
WHERE  runtime > (SELECT AVG(runtime) FROM Movie);

-- Find actors who have appeared in more than 2 movies:
SELECT a.firstName, a.surname, COUNT(*) AS MovieCount
FROM   Actor a
INNER JOIN Casting c ON a.actorNo = c.actorNo
GROUP BY a.actorNo, a.firstName, a.surname
HAVING COUNT(*) > 2;

SQL Execution Order