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.