combines columns from one or more tables into a new table
AS statement
display column with new name
INNER JOINS
results with the set of records that match in both tables
// result of customers who had a transaction
SELECT payment_id, payment.customer_id, first_name
FROM customer
INNER JOIN payment
ON customer.customer_id = payment.customer_id;OUTER JOINS
FULL OUTER JOINS
grabs everything
SELECT * FROM customer
FULL OUTER JOIN payment
ON customer.customer_id = payment.customer_id
WHERE customer.customer_id IS null
OR payment.payment_id IS null;LEFT OUTER JOIN
results in the set of records in the left table(from the match with right table)
// order matters!
// films are not in our inventory (use case)
SELECT film.film_id, title, inventory_id, store_id FROM film
LEFT OUTER JOIN inventory
ON inventory.film_id = film.film_id
WHERE inventory.film_id IS nullRIGHT OUTER JOIN
results in the set of records in the right table(from the match with left table)
// order matters!
// films are not in our inventory (use case)
SELECT film.film_id, title, inventory_id, store_id FROM inventory
RIGHT OUTER JOIN film
ON inventory.film_id = film.film_id
WHERE film.film_id IS nullExamples
- It works, but use outer join when you need every information (by using WHERE customer.addressid IS null OR address.addressid IS null
SELECT district, email from address
FULL OUTER JOIN customer
ON address.address_id = customer.address_id
WHERE address.district = 'California';SELECT first_name, last_name, film_id FROM actor
INNER JOIN film_actor
ON actor.actor_id = film_actor.actor_id;- Two joins
SELECT title, first_name, last_name FROM actor
INNER JOIN film_actor
ON actor.actor_id = film_actor.actor_id
INNER JOIN film
ON film_actor.film_id = film.film_id
WHERE actor.first_name = 'Nick' AND actor.last_name = 'Wahlberg'