SQL in a Nutshell, 3rd Edition

Codd’s Rules in Action: Simple SELECT Examples

Up to this point, this chapter has focused on the individual aspects of a relational database platform as defined by Codd and implemented under ANSI SQL. This section presents a high-level overview of the most important SQL statement, SELECT, and some of its most salient points—namely, the relational opera ions known as projections, selections, and joins:

Projection

Retrieves specific columns of data

Selection

Retrieves specific rows of data

Join

Returns columns and rows from two or more tables in a single result set

Although at first glance it might appear as though the SELECT statement deals only with the relational selection operation, in actuality, SELECT deals with all three operations.

The following statement embodies the projection operation by selecting the first and last names of an author, plus his home state, from the authors table:

SELECT au_fname, au_lname, state
FROM   authors

The results from any such SELECT statement are presented as another table of data:

au_fname             au_lname                            state
----------------  ----------------------------  ----------------
Johnson              White                               CA
Marjorie             Green                               CA
Cheryl               Carson                              CA
Michael              O'Leary                             CA
Meander              Smith                               KS
Morningstar          Greene                              TN
Reginald             Blotchet-Halls                      OR
Innes                del Castillo                        MI

The resulting data is sometimes called a result set, work table, or derived table, differentiating it from the base table in the database that is the target of the SELECT statement.

It is important to note that the relational operation of ...