JOIN Practice 2

Goals

• Apply FULL OUTER JOIN to find gaps between two tables
• Use SELF JOIN to relate rows within the same table
• Combine multiple tables with UNION to build unified result sets
• Chain four or more tables in a single query
• Use EXCEPT as an alternative to NULL-based anti-join patterns

Warm-up

Run this quick query to confirm the database is loaded and understand the inventory distribution:

SELECT store_id, COUNT(*) AS inventory_count
FROM inventory
GROUP BY store_id
ORDER BY store_id;

Note how many copies each store carries. You will use this information in Step 5.

Steps

Step 1 — Films With No Inventory Copies (Medium)

Objective: Use FULL OUTER JOIN to find films that have no inventory records at all.

Tables involved: film, inventory

A film exists in the film table but may have zero rows in inventory (no physical copies exist at any store). FULL OUTER JOIN exposes this gap.

Task: Return film.film_id, film.title, and inventory.inventory_id for all rows after a FULL OUTER JOIN. Then filter to show only films where inventory.inventory_id IS NULL — those are the films with no copies.

Expected shape:

SELECT f.film_id, f.title, i.inventory_id
FROM film f
FULL OUTER JOIN inventory i ON f.film_id = i.film_id
WHERE i.inventory_id IS NULL
ORDER BY f.film_id;

Step 2 — Customer Pairs in the Same District (Medium)

Objective: Use SELF JOIN to find all pairs of customers who live in the same district.

Tables involved: customer, address

Each customer has an address_id that points to the address table. The address table has a district column. A self join on customer (with two different aliases) lets you compare every customer against every other.

Task: Return customer1_name (concatenated), customer2_name, and district. Use c1.customer_id < c2.customer_id to avoid duplicate pairs and self-matches. Limit to 20 rows.

Expected shape:

SELECT c1.first_name || ' ' || c1.last_name AS customer1_name,
       c2.first_name || ' ' || c2.last_name AS customer2_name,
       a1.district
FROM customer c1
JOIN address  a1 ON c1.address_id = a1.address_id
JOIN customer c2 ON c1.customer_id < c2.customer_id
JOIN address  a2 ON c2.address_id = a2.address_id
WHERE a1.district = a2.district
LIMIT 20;

Step 3 — Unified Name Directory From Three Tables (Hard)

Objective: Build a single deduplicated list of all people in the database — actors, customers, and staff — each labelled with their source table.

Tables involved: actor, customer, staff

Task: Write a query using UNION (not UNION ALL) that returns first_name, last_name, and a source column with the value 'actor', 'customer', or 'staff' respectively. Order by last_name, then first_name. The result should deduplicate any rows where the exact name and source are identical.

Expected shape:

SELECT first_name, last_name, 'actor'    AS source FROM actor
UNION
SELECT first_name, last_name, 'customer' AS source FROM customer
UNION
SELECT first_name, last_name, 'staff'    AS source FROM staff
ORDER BY last_name, first_name;

Step 4 — Full Rental Detail: Film, Customer, Staff, City (Hard)

Objective: Chain six tables to produce a complete rental record showing every piece of context for each transaction.

Tables involved: rental, inventory, film, customer, staff, store, address, city

Task: Return the following columns for every rental: rental.rental_id, film.title, customer.first_name || ' ' || customer.last_name AS customer_name, staff.first_name || ' ' || staff.last_name AS staff_name, city.city AS store_city. Order by rental_id. Limit to 25 rows.

Expected shape:

SELECT r.rental_id,
       f.title,
       c.first_name  || ' ' || c.last_name  AS customer_name,
       st.first_name || ' ' || st.last_name AS staff_name,
       ci.city AS store_city
FROM rental r
INNER JOIN inventory i  ON r.inventory_id = i.inventory_id
INNER JOIN film f       ON i.film_id       = f.film_id
INNER JOIN customer c   ON r.customer_id   = c.customer_id
INNER JOIN staff st     ON r.staff_id      = st.staff_id
INNER JOIN store s      ON st.store_id     = s.store_id
INNER JOIN address a    ON s.address_id    = a.address_id
INNER JOIN city ci      ON a.city_id       = ci.city_id
ORDER BY r.rental_id
LIMIT 25;

Step 5 — Films in Store 1 But Not in Store 2 (Hard)

Objective: Find films that have inventory copies at store 1 but no inventory copies at store 2.

Tables involved: film, inventory

Task: Write a query using EXCEPT (or FULL OUTER JOIN with NULL filter) that returns the film_id and title of films available at store 1 but absent from store 2's inventory. Order by title.

Expected shape:

SELECT f.film_id, f.title
FROM film f
INNER JOIN inventory i ON f.film_id = i.film_id
WHERE i.store_id = 1

EXCEPT

SELECT f.film_id, f.title
FROM film f
INNER JOIN inventory i ON f.film_id = i.film_id
WHERE i.store_id = 2

ORDER BY title;

SELECT DISTINCT f.film_id, f.title
FROM film f
INNER JOIN inventory i1 ON f.film_id = i1.film_id AND i1.store_id = 1
LEFT  JOIN inventory i2 ON f.film_id = i2.film_id AND i2.store_id = 2
WHERE i2.inventory_id IS NULL
ORDER BY f.title;

Step 6 — Categories With No Films (Bonus/Hard)

Objective: Find categories that have no films associated with them.

Tables involved: category, film_category

Task: Use a LEFT JOIN with NULL check to identify categories that have zero entries in film_category. Return the category_id and name of those orphaned categories.

Expected shape: This result may be empty in the standard DVD Rental dataset (all categories have at least one film). Confirm the finding either way — an empty result is a correct and meaningful answer.

SELECT c.category_id, c.name
FROM category c
LEFT JOIN film_category fc ON c.category_id = fc.category_id
WHERE fc.film_id IS NULL
ORDER BY c.name;

Bonus Tasks

Bonus 1 — Customers in the Same City (Extended Self Join)

Extend Step 2 to also join through city and display the actual city name instead of just the district. How many pairs share the same city?

Bonus 2 — Films Available at Neither Store

Using FULL OUTER JOIN or EXCEPT logic, find films that have no inventory at either store (they exist in film but nowhere in inventory). This extends the logic from Step 1.