Sunday, June 16, 2013

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

"I am left out; for me nothing remains."
-- Shakespeare, Henry VI Part I
Act I, Scene I, Line 174

I was LEFT OUTER


We are still on ON and JOIN in the SELECT hierarchy.

  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
LEFT OUTER JOINs were one of the first feature added to SQL.  Customers wanted a way to retrieve data in when the related data was not available.  I could take a thousand words to further describe this, but I'll let this picture do the talking.

With a LEFT OUTER JOIN the complete left side is returned, even the parts that do have a matching record in the right side.  Let's see how we can use this with our plays and playwrights.

Again, 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)
;

And their 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')
;

The Joy of LEFT OUTER JOIN


It seems that our data has some issues.  Poor Jacopone da Todi has no plays in our play table.  If we did an INNER JOIN between plays and playwrights Jocopone would be left out.  This is where a LEFT OUTER JOIN comes into play.  If we want to get a list of all the playwrights including those that do not have plays in the plays table we would do a LEFT OUTER JOIN between playwrights and plays (with playwrights on the left side and plays on the right).

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

This will give us Jocopone and any other playwright that does not have a play in our plays table.  The play under Jocopone will show up as NULL meaning no data.

PLAYWRIGHTPLAY
Oscar WildeThe Importance of Being Earnest
EuripidesMedea
EuripidesWomen of Troy
EuripidesOrestes
EuripidesPhoenician Women
EuripidesBacchae
AeschylusSeven against Thebes
AeschylusAgamemnon
AeschylusThe Libation Bearers
AeschylusThe Eumenides
AeschylusPrometheus Bound
SophoclesOedipus Tyrannus
SophoclesOedipus at Colonus
SophoclesAntigone
SophoclesElectra
William ShakespeareThe Tempest
William ShakespeareA Midsummer Night's Dream
William ShakespeareThe Merchant of Venice
William ShakespeareAs You Like It
William ShakespeareRomeo and Juliet
William ShakespeareJulius Caesar
William ShakespeareMacbeth
William ShakespeareHamlet
William ShakespeareOthello
Jacopone da Todi(null)

Likewise, if we want to have the plays without playwrights returned (Edward III and The Second Shepherds' Play in our plays table), we would want to do a LEFT OUTER JOIN with the plays this time on the left and the playwrights on the right.

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

This will give us our missing plays with NULL as the playwright.

PLAYPLAYWRIGHT
The Importance of Being EarnestOscar Wilde
MedeaEuripides
Women of TroyEuripides
OrestesEuripides
Phoenician WomenEuripides
BacchaeEuripides
Seven against ThebesAeschylus
AgamemnonAeschylus
The Libation BearersAeschylus
The EumenidesAeschylus
Prometheus BoundAeschylus
Oedipus TyrannusSophocles
Oedipus at ColonusSophocles
AntigoneSophocles
ElectraSophocles
The TempestWilliam Shakespeare
A Midsummer Night's DreamWilliam Shakespeare
The Merchant of VeniceWilliam Shakespeare
As You Like ItWilliam Shakespeare
Romeo and JulietWilliam Shakespeare
Julius CaesarWilliam Shakespeare
MacbethWilliam Shakespeare
HamletWilliam Shakespeare
OthelloWilliam Shakespeare
The Second Shepherds' Play(null)
Edward III(null)

(On SQL Fiddle)



Again looking at our Venn diagram from before, we see that the left side and center are colored in red, this is what is meant by a LEFT OUTER JOIN, give me both what matches (the center or INNER JOIN results) and what is unmatched on the left side or the table named on the FROM clause.


Posted by
Labels: , ,