Join

A foreign key constraint can optionally be created to verify that the values in one table exist in another table.

Please note that it is not necessary to have a foreign key constraint in place in order to join two tables.

Cartesian Product

select first_name, last_name, address
from customer as c join address as a;

599 customers x 603 addresses = 361,197 permutations.

Rarely used.

Inner Joins

select first_name, last_name, address
from customer as c
	inner join address as a

	on c.address_id = a.address_id;
/* or */
	using (address_id);

The ANSI Join Syntax

select c.first_name, c.last_name, a.address
from customer c, address a
where c.address_id = a.address_id;
	and a.postal_code = 52137;

/* or */
	where a.postal_code = 52137;

Joinning 3 or More Tables

select first_name, last_name, city
from customer as c
	inner join address as a
	on c.address_id = a.address_id
  inner join city as ct
  on a.city_id = ct.city_id;

Using Subqueries as Tables

select first_name, last_name, address, city
from customer as c
	inner join
		(select address_id, address, city
		from address as a
			inner join city as ct
			on a.city_id = ct.city_id
		where a.district = 'California') as addr
	on c.address_id = addr.address_id;

Using the Same Table Twice

If you want to find all of the films in which one of the two specific actors appear:

select title
from actor as a
	inner join film_actor as fa
    on a.actor_id = fa.actor_id
    inner join film as f
    on fa.film_id = f.film_id
where (first_name = 'PENELOPE' and last_name = 'GUINESS')
	or (first_name = 'NICK' and last_name = 'WAHLBERG');

Now you want to retrieve only those films in which both of these actors appeared:

select f.title
from film as f
	inner join film_actor as fa1
    on f.film_id = fa1.film_id
    inner join actor as a1
    on fa1.actor_id = a1.actor_id
    inner join film_actor as fa2
    on f.film_id = fa2.film_id
    inner join actor as a2
    on fa2.actor_id = a2.actor_id
where (a1.first_name = 'CATE' and a1.last_name = 'MCQUEEN')
	and (a2.first_name = 'NICK' and a2.last_name = 'WAHLBERG');