Sunday, June 30, 2013

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

"Yet marked I where the bolt of Cupid fell:

It fell upon a little western flower,
Before, milk-white; now purple with love's wound:
And maidens call it ‘ love in idleness.’
Fetch me that flower – the herb I showed thee once
"
-- Shakespeare, A Midsummer Night's Dream
Act II, Scene I, Lines 165-169

WHERE is my data?


In our journey through the SELECT clause we have finally moved on to the WHERE clause.

  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
The simplest way to think of the WHERE clause is think of it as a filter.

Filtering

Given our data set of 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')
;

If we wanted to only get the playwrights born in BC we would apply a filter using the WHERE clause which states only give me records where the birthEra has the value of BC.



SELECT *
  FROM playwrights
  WHERE birthera = 'BC'
;

(on SQL Fiddle)

In our WHERE clause we state that the value must equal BC, yielding the records of Euripides, Aeschylus, and Sophocles.

Likewise we can create multi-conditional statements to apply even more filtering rules against our data set.  Say we want to limit our records to just the playwrights born after the founding of America.


SELECT *
  FROM playwrights
  WHERE birthera = 'AD'
    AND birthyear > 1775
;

(on SQL Fiddle)

We can use a logical AND to state that the birthEra must be AD and the birthYear must be after 1775.  We can of course also write the SQL statement using a greater than or equal to 1776.

SELECT *
  FROM playwrights
  WHERE birthera = 'AD'
    AND birthyear >= 1776
;

Both will yield the same resulting record set.

Sunday, June 23, 2013

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
;

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)
(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.

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.


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.

Sunday, June 2, 2013

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

"When our deep plots do pall, and that should learn us"
-- Shakespeare, Hamlet
Act V, Scene II, Line 9

If you want to truly learn SQL, "[y]ou must unlearn what you have learned."  All to often the way that we learn SQL is by figuring out what we need to know to get by.  Most developers do not even realize that SQL is a declarative language.  This series will learn you some SQL (title based on Learn You Some Haskell).


From here to the moon


Let's start with SELECT.  Here is the logical order in which SELECT is processed:


  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
Hmmm, FROM is at the top!  But, I was taught SELECT first.  Hell, it is even the first statement that I type in a SELECT query.  Just what is going on here?!?


If you want to know more about something you first need to know that it exist.


What does that mean?  Simple, in order to learn more about anything, you first need to know that the thing exist.  It is impossible to find out more information about something you do not even know exist.  Say you wanted to know more about Rat Terriers, you would first have to know that there exist a dog breed called Rat Terriers.  If you did not know of the existence of Rat Terriers you would not even know to look for information on Rat Terriers, let alone where to find more information on them.

What does this have to do with FROM?!?  In order to retrieve information about data (which is what the SELECT statement is all about), we need to know where to look for it.  Even more importantly we need to know that it exist!

What the FROM clause does is say we know that this data set exist and this is where it is.  The FROM clause is are first step in our SELECT journey.


The play's the thing


Let's look at some examples using FROM.

Given the following tables (which should not be fully clear from the examples thus far, but we need something to pull data from):


Playwrights




CREATE TABLE plays (
  id [int] IDENTITY(1,1)
 ,name [varchar](100)
 ,playwrightId [int]
);


Plays


CREATE TABLE playwrights (
  id [int] IDENTITY(1,1)
 ,name [varchar](100)
 ,birthYear [int]
 ,birthEra [char](2)

);


If place the following data into these tables (again we need something to pull from):


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


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)

;

Let's get the all the playwrights.

SELECT *
  FROM playwrights

;

(On SQL Fiddle)

We see that we use the FROM clause to identify where we would like to pull our data set from.  In this case it is from the playwrights data set.

Now let's get all the plays.

SELECT *
  FROM plays

;

(On SQL Fiddle)

Again we see that the FROM clause is used to identify where we would like to pull our data set from.  In this case we want the plays.

That is all there is to the FROM clause, it is used to say which data set to pull from.