Sunday, June 9, 2013

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

"Yea, brought her hither
That thou mightst join her hand with his
Whose heart within her bosom is."

-- Shakespeare, As You Like It
Act V, Scene IV, Lines 110-112




JOIN ON and on, and on and on


Continuing down the SELECT hierarchy, we now find ourselves at ON and JOIN.  I know of no way to just talk about ON without JOIN and vise-versa.

  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
Historically when SQL was first created there was only one kind of JOIN, which we now term as INNER JOIN.   An INNER JOIN is used when you want to bring together two tables on a value.  You can think of them as a Venn diagram.
Let's look at our data and figure out how to join them.

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


Their 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)
;

Plays and their playwrights


If we want to join the plays to their playwrights we would want to use an INNER JOIN, ON the playwrigthId, which will join the id on the playwrights to plays (I know this because I created the tables in that way).

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

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

Notice that the playwright Jacopone da Todi and the two plays The Second Shepherd's Play and Edward III are not returned.  The playwright Jacopone da Todi does not have any matching plays in our plays table.  Likewise, the two plays The Second Shepherd's Play and Edward III do not have any matching playwrights in our playwrights table and thus are not returned.  This is what it means to do an INNER JOIN between to tables, the value in the ON clause must match in both sets.

Look at the Venn diagram again, the spot that is red in the middle that is the overlap between the two sets.  That is what an INNER JOIN is.