SQL: JOINS - OUTER JOIN

---

SQL: JOINS - OUTER JOIN

OUTER JOINs

FULL OUTER JOIN

The FULL OUTER JOIN produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

Syntax:

-- FULL OUTER JOIN
SELECT *
FROM TableA
FULL OUTER JOIN TableB ON TableA.name = TableB.name;

FULL OUTER JOIN with WHERE

The FULL OUTER JOIN with WHERE produces the set of records unqiue to Table A and Table B, we perform the same full outer join, then exclude the records we don’t want from both sides via a where clause.

Syntax:

-- FULL OUTER JOIN
SELECT *
FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableB.id IS null OR TableB.id IS null;

LEFT OUTER JOIN

The LEFT OUTER JOIN produces a complete set of records from Table A, with the matching records(where available) in Table B. If there is no match, the right side will contain null.

Syntax:

-- FULL OUTER JOIN
SELECT *
FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name;

LEFT OUTER JOIN with WHERE

The LEFT OUTER JOIN with WHERE produces the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don’t want from the right side via a where clause.

Syntax:

-- FULL OUTER JOIN
SELECT *
FROM TableA
LEFT OUTER JOIN TableB ON TableA.name = TableB.name
WHERE TableB.id IS null;

OUTER JOIN Example

Example: Perform a LEFT OUTHER JOIN with the film and inventory tables to obtain a copy of all movies that are not in iventory.

-- Perform a LEFT OUTHER JOIN with the film and inventory tables.
SELECT film.film_id, film.title, inventory_id
FROM film
LEFT OUTER JOIN inventory ON inventory.film_id = film.film_id
WHERE inventory.film_id IS NULL
ORDER BY film.film_id;
-- Second Solution
SELECT film.film_id, film.title, inventory_id
FROM film
LEFT OUTER JOIN inventory ON inventory.film_id = film.film_id
WHERE inventory_id is NULL
ORDER BY film.title;

##    film_id                  title inventory_id
## 1       14         Alice Fantasia           NA
## 2       33            Apollo Teen           NA
## 3       36         Argonauts Town           NA
## 4       38          Ark Ridgemont           NA
## 5       41   Arsenic Independence           NA
## 6       87      Boondock Ballroom           NA
## 7      108          Butch Panther           NA
## 8      128          Catch Amistad           NA
## 9      144    Chinatown Gladiator           NA
## 10     148         Chocolate Duck           NA
## 11     171   Commandments Express           NA
## 12     192       Crossing Divorce           NA
## 13     195        Crowds Telemark           NA
## 14     198       Crystal Breaking           NA
## 15     217             Dazed Punk           NA
## 16     221 Deliverance Mulholland           NA
## 17     318      Firehouse Vietnam           NA
## 18     325          Floats Garden           NA
## 19     332  Frankenstein Stranger           NA
## 20     359     Gladiator Westward           NA
## 21     386              Gump Date           NA
## 22     404          Hate Handicap           NA
## 23     419            Hocus Frida           NA
## 24     495       Kentuckian Giant           NA
## 25     497       Kill Brotherhood           NA
## 26     607            Muppet Mile           NA
## 27     642         Order Betrayed           NA
## 28     669          Pearl Destiny           NA
## 29     671        Perdition Fargo           NA
## 30     701          Psycho Shrunk           NA
## 31     712      Raiders Antitrust           NA
## 32     713          Rainbow Shock           NA
## 33     742          Roof Champion           NA
## 34     801          Sister Freddy           NA
## 35     802            Sky Miracle           NA
## 36     860       Suicides Silence           NA
## 37     874           Tadpole Park           NA
## 38     909       Treasure Command           NA
## 39     943      Villain Desperate           NA
## 40     950           Volume House           NA
## 41     954              Wake Jaws           NA
## 42     955           Walls Artist           NA