Learn You Some SQL For Great Good - Unlearn What You Have Learned OR How Select Logically Works (the ON and JOIN clause--FULL OUTER JOIN)

"The King is full of grace and fair regard."
-- Shakespeare, Henry V
Act I, Scene I, Line 22

I am a FULL OUTER

I have good news and bad news.  The good news is that we are about to go over the FULL OUTER JOIN.  The bad news is that in the SELECT hierarchy, we are still on ON / JOIN.

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

Since I am frugal, I shall use a picture yet again to speak for me as to what a FULL OUTER JOIN is.

As you can see both the left and the right side are fully colored in.  What a FULL OUTER JOIN does is return both tables and where the values do not join together it returns NULLs.

Fully joining the playwright to their plays

If we do a FULL OUTER JOIN between are playwrights and our plays we should be able to get the total sets between both tables.

Our Playwrights

INSERT INTO playwrights
(name, birthYear, birthEra)
VALUES
  ('Oscar Wilde', 1854, 'AD')
 ,('Euripides', 480, 'BC')
 ,('Aeschylus', 525, 'BC')
 ,('Sophocles', 497, 'BC')
 ,('William Shakespeare', 1564, 'AD')
 ,('Jacopone da Todi', 1230, 'AD')
;

Our Plays

INSERT INTO plays
(name, playwrightId)
VALUES
  ('The Importance of Being Earnest', 1)
 ,('Medea', 2)
 ,('Women of Troy', 2)
 ,('Orestes', 2)
 ,('Phoenician Women', 2)
 ,('Bacchae', 2)
 ,('Seven against Thebes', 3)
 ,('Agamemnon', 3)
 ,('The Libation Bearers', 3)
 ,('The Eumenides', 3)
 ,('Prometheus Bound', 3)
 ,('Oedipus Tyrannus', 4)
 ,('Oedipus at Colonus', 4)
 ,('Antigone', 4)
 ,('Electra', 4)
 ,('The Tempest', 5)
 ,('A Midsummer Night''s Dream', 5)
 ,('The Merchant of Venice', 5)
 ,('As You Like It', 5)
 ,('Romeo and Juliet', 5)
 ,('Julius Caesar', 5)
 ,('Macbeth', 5)
 ,('Hamlet', 5)
 ,('Othello', 5)
 ,('The Second Shepherds'' Play', 0)
 ,('Edward III', 0)
;

To join or not to join

If we look above we see that Jacopone da Todi has not plays in our plays table and likewise The Second Shepherds' Play and Edward III have no playwrights in our playwrights table.  When we do a FULL OUTER JOIN we will see that these records will have NULLs for the parts in the records set being returned for which they have no matching pieces.

SELECT
   p.name AS play
  ,a.name AS playwright
  FROM plays AS p
  FULL OUTER JOIN playwrights AS a
    ON p.playwrightId = a.id
;

PLAY	PLAYWRIGHT
The Importance of Being Earnest	Oscar Wilde
Medea	Euripides
Women of Troy	Euripides
Orestes	Euripides
Phoenician Women	Euripides
Bacchae	Euripides
Seven against Thebes	Aeschylus
Agamemnon	Aeschylus
The Libation Bearers	Aeschylus
The Eumenides	Aeschylus
Prometheus Bound	Aeschylus
Oedipus Tyrannus	Sophocles
Oedipus at Colonus	Sophocles
Antigone	Sophocles
Electra	Sophocles
The Tempest	William Shakespeare
A Midsummer Night's Dream	William Shakespeare
The Merchant of Venice	William Shakespeare
As You Like It	William Shakespeare
Romeo and Juliet	William Shakespeare
Julius Caesar	William Shakespeare
Macbeth	William Shakespeare
Hamlet	William Shakespeare
Othello	William Shakespeare
The Second Shepherds' Play	(null)
Edward III	(null)
(null)	Jacopone da Todi

(On SQL Fiddle)

Looking at the results we see that The Second Shepherds' Play and Edward III have no matching playwright, while Jacopone da Todi has not matching plays.  Quit often finding these non-matching records is how a FULL OUTER JOIN is used.