Selection and projection

In relational terminology, selection is defined as taking the horizontal subset of rows of a single table that satisfies a particular condition. This kind of SELECT statement returns some of the rows and all the columns in a table. Selection is implemented through the WHERE clause of a SELECT statement, as the following figure shows.

Figure 1. Query

SELECT * FROM customer WHERE state = 'NJ';

The result contains the same number of columns as the customer table, but only a subset of its rows. In this example, DB-Access displays the data from each column on a separate line.

Figure 2. Query result

customer_num  119
fname         Bob
lname         Shorter
company       The Triathletes Club
address1      2405 Kings Highway
address2
city          Cherry Hill
state         NJ
zipcode       08002
phone         609-663-6079

customer_num  122
fname         Cathy
lname         O‘Brian
company       The Sporting Life
address1      543d Nassau
address2
city          Princeton
state         NJ
zipcode       08540
phone         609-342-0054

In relational terminology, projection is defined as taking a vertical subset from the columns of a single table that retains the unique rows. This kind of SELECT statement returns some of the columns and all the rows in a table.

Projection is implemented through the projection list in the Projection clause of a SELECT statement, as the following figure shows.

Figure 3. Query

SELECT city, state, zipcode FROM customer;

The result contains the same number of rows as the customer table, but it projects only a subset of the columns in the table. Because only a small amount of data is selected from each row, DB-Access is able to display all of the data from the row on one line.

Figure 4. Query result

city            state zipcode

Sunnyvale       CA    94086
San Francisco   CA    94117
Palo Alto       CA    94303
Redwood City    CA    94026
Los Altos       CA    94022
Mountain View   CA    94063
Palo Alto       CA    94304
Redwood City    CA    94063
Sunnyvale       CA    94086
Redwood City    CA    94062
Sunnyvale       CA    94085
⋮
Oakland         CA    94609
Cherry Hill     NJ    08002
Phoenix         AZ    85016
Wilmington      DE    19898
Princeton       NJ    08540
Jacksonville    FL    32256
Bartlesville    OK    74006

The most common kind of SELECT statement uses both selection and projection. A query of this kind returns some of the rows and some of the columns in a table, as the following figure shows.

Figure 5. Query

SELECT UNIQUE city, state, zipcode
   FROM customer
   WHERE state = 'NJ';

Figure 6 contains a subset of the rows and a subset of the columns in the customer table.

Figure 6. Query result

city            state zipcode 

Cherry Hill     NJ    08002  
Princeton       NJ    08540