Outer Joins

select f.film_id, f.title, count(*)
from inventory as i
	inner join film as f
    on f.film_id = i.film_id
group by f.film_id;

Context: Counting the number of available copies of each film by joining these two tables.

There are 1000 films in total, but only 958 distinct rows are returned, indicate that the other 42 are not available.

select f.film_id, f.title, count(i.inventory_id)
from inventory as i
	right outer join film as f
    on f.film_id = i.film_id
group by f.film_id;

Context: You still want to return all 1000 films, regardless of availability.

• right outer instructs server to include all rows on the right side of the join, then add in columns from table on the left side if the join is successful.
• Changing the count (*) to count(i.inventory_id) helps count the number of non-null values.

<aside> 💡 outer join will add column values (which can be null) without restricting the number of rows returned by the query.

</aside>

Left or Right Outer Joins

Both keywords work with the same purpose of pointing out which table is allowed to include all rows, and which will include their columns in.

Three-Way Outer Joins

select f.film_id, f.title, count(i.inventory_id), r.rental_date
from film as f
	right outer join inventory as i
    on f.film_id = i.film_id
    left outer join rental as r
    on r.inventory_id = i.inventory_id
group by f.film_id;

Cross Joins

A Cartesian product, which is essentially the result of joining multiple tables without specifying any join conditions. Reasons are ranged from accidents to intentions.

select c.name, l.name
from language as l
	cross join category as c;
# 96 rows (16 category rows × 6 language rows).

select days.dt, count(r.rental_id)
from rental as r
right outer join
	(SELECT date_add('2005-01-01', interval (ones.num + tens.num + hundreds.num) day) dt
	from
		(SELECT 0 num UNION ALL
		SELECT 1 num UNION ALL
		SELECT 2 num UNION ALL
		SELECT 3 num UNION ALL
		SELECT 4 num UNION ALL
		SELECT 5 num UNION ALL
		SELECT 6 num UNION ALL
		SELECT 7 num UNION ALL
		SELECT 8 num UNION ALL
		SELECT 9 num) ones
	cross join
		(SELECT 0 num UNION ALL
		SELECT 10 num UNION ALL
		SELECT 20 num UNION ALL
		SELECT 30 num UNION ALL
		SELECT 40 num UNION ALL
		SELECT 50 num UNION ALL
		SELECT 60 num UNION ALL
		SELECT 70 num UNION ALL
		SELECT 80 num UNION ALL
		SELECT 90 num) tens
	CROSS JOIN
		(SELECT 0 num UNION ALL
		SELECT 100 num UNION ALL
		SELECT 200 num UNION ALL
		SELECT 300 num) hundreds
	where date_add('2005-01-01', interval (ones.num + tens.num + hundreds.num) day) < '2006-01-01'
	order by 1) days
on days.dt = date(r.rental_date)
group by days.dt
order by 1;

Context: Generate a 365-days column using cross join and create a report on number of rentals happenned using that column.

Natural Joins