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.
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE or WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- 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
;
| 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 |
(On SQL Fiddle)
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.
