"
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.
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
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.
