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

"Though thanks to all, must I select from all. The rest
Shall bear the business in some other fight,"
-- Shakespeare, Coriolanus
Act I, Scene VI, Lines 81-82

SELECT information on selections

We are finally on the term SELECT.  Yep, this is normally the first thing covered when one learns about SQL, but as you now know SELECT is logically the 8th thing that gets looked at when doing a SELECT SQL query.

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

SELECT has a lot of different things going on, so we'll spend a bit of time on SELECT.  For this post we'll just cover projections.

Projections OR how to impress people when talking about SELECT

A projection is a fancy relational algebra term for something that is very easy to understand.  A project can be thought of as a list of things that one wants from a set.

Given the following tables (or data sets):

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

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

We can say I am only interested in the name column (or attribute) of the plays table with the following query.

SELECT name
  FROM plays
;

(on SQL Fiddle)

This will limit are results to just the plays in the plays table.

Like wise we can join the two tables together and say which columns (attributes) we want for each.

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

(on SQL Fiddle)

Notice we just do not speak of the things we do not want, SQL is a declarative language (yep, even Forbes think declarative programming is a good thing), this means we describe what we want and not how to do it.

Also notice the AS term, this term allows us to alias things.  If we did not have the alias of p and w on the plays and playwrights tables we would not be able to distinguish between the plays' name column and the playwrights' name column or the id on plays and the id on playwrights

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

(on SQL Fiddle)

We get the following error:

Ambiguous column name 'id'.: SELECT name AS play ,name AS playwright FROM plays INNER JOIN playwrights ON playwrightId = id

There is no way for the database management system to be able to tell the difference between the plays' id and the playwrights' id.  To fix this we can do the following.

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

(on SQL Fiddle)

By saying which table they come from we are able to use column name and id in the query above.  This is a bit wordy which is why we have the AS term.  AS allows us to alias the fully name of the table as a different value.  Let's look back at the original query.

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

(on SQL Fiddle)

In the above query we have alias plays AS p and playwrights AS w.  Now when we go to use the playwrights id we can simply say w.id, likewise p.name is the plays' name column and w.name is the playwrights' name.  Notice that we further alias the projections of p.name AS play and w.name AS playwright, this will change the attributes on the result set of the query to play for p.name and playwright for w.name.