Categories Software Related Stories SQL: The Universal Database Language -- 11/3/98 Next: Defining Selection Criteria

Continued from The INSERT statement

The SELECT statement extracts specified columns and rows from one or more tables. Since querying and retrieving data is one of the most important functions in database management, the SELECT statement is the SQL workhorse. In fact, people who access databases solely to analyze data or generate reports may never have to learn another SQL statement.

The result of a SELECT statement is always another table. During execution, the system selects columns and rows matching your criteria from the database and places the results into a temporary table. In the case of direct SQL, it displays the results on the terminal screen or sends them to a printer or file. It can also place the results into a named table in conjunction with other SQL statements.

The SELECT statement is quite powerful. Although its name implies that it performs only one of the relational algebra operations we mentioned in Part 1, select (also known as restrict), it actually implements two other relational operations as well: project and join. We will discuss SELECT and PROJECT operations below. (JOINs are more advanced and beyond the scope of this article.) The SELECT statement can also perform aggregate calculations and sort data for reports.

The minimum syntax of a SELECT statement is as follows:

SELECT columns FROM tables;

When you execute a SELECT statement in this form, the system returns a result table made up of the selected columns and all of the rows from the table(s) you specified. This is the form that implements the relational PROJECT operation.

Let's look at some examples using the EMPLOYEES table shown in Figure 1. (This is the table we will use for all of our SELECT statements. The actual results of our queries are given in Figures 2 and 3. We will be referring to these result tables in the rest of our examples. )

Suppose you wanted to see a list of branch offices where employees work. Here's how you would write the query in SQL:

SELECT BRANCH_OFFICE
   FROM EMPLOYEES;

Executing the above SELECT statement will give you the result shown in Figure 2 (below), Table 2.

Since we specified only one column in the SELECT statement, our result table has just one column. Notice that there are duplicate rows in the result table. This is because several employees work at the same branches. (Remember that SQL returns the values from all of the selected rows.) To eliminate duplicates in the result set, you can add the DISTINCT clause to your SELECT statement:

SELECT DISTINCT BRANCH_OFFICE 
   FROM EMPLOYEES ;

This query yields the results shown in Table 3.

Now you've eliminated the duplicates, but the result isn't in any particular order. What if you wanted to see the branches listed alphabetically? Use the ORDER BY clause to sort results in ascending or descending order:

SELECT DISTINCT BRANCH_OFFICE
   FROM EMPLOYEES
   ORDER BY BRANCH_OFFICE ASC ;

The result of this query is shown in Table 4. Notice how we placed the column name BRANCH_OFFICE after ORDER BY. That's the column we want to sort on. Why did we have to repeat the column name when it's the only column in the result table? Because we can sort the results on any column in the table even though we're not displaying it. The keyword ASC after BRANCH_OFFICE stands for ascending order. If you wanted to sort the column in descending order, you would have used DESC instead.

Also, we should point out that the ORDER BY clause sorts only the results in the temporary table; it doesn't affect the original table.

Suppose you wanted to see a list of employees sorted by branch office, from the highest-paid employees down to the lowest-paid. Within each salary bracket, you also wanted to see the employees listed by date of hire, starting with the most recent hires. Here's the statement you'd use:

SELECT BRANCH_OFFICE, FIRST_NAME,
   LAST_NAME, SALARY, HIRE_DATE
   FROM EMPLOYEES
   ORDER BY SALARY DESC,
   HIRE_DATE DESC ;

Here we're selecting and sorting multiple columns. The sorting priority is determined by the order in which you place the columns. SQL will first sort the results by the first column in the list. If it finds any duplicates in the first column, it will sort them by the second column; if it finds any duplicates in the second, it will sort them by the third; and so on. The result of this query is shown in Table 5.

Typing out all of the column names in a long table can be tedious, so SQL lets you use an asterisk (*) whenever you want to select all the columns in a table:

SELECT * FROM EMPLOYEES ;

This query returns the entire EMPLOYEES table, as shown in Table 1.

To recap what we've covered so far, let's update the SELECT statement syntax we gave you at the beginning of this section:

SELECT [DISTINCT] (column [ {, column} ] ) | *
   FROM table [ {, table} ]
   [ORDER BY column [ASC] | DESC [ {, column [ASC] | DESC } ] ] ;

(The vertical bar designates an alternative, allowing one choice.)

FIGURE 2: Tables 2 through 4 show the results of our single-column SELECT examples.

SELECT BRANCH_OFFICE FROM EMPLOYEES

Table 2 BRANCH_OFFICE
        Los Angeles
        Boston
        Boston
        Los Angeles
        Chicago
        Boston
        Chicago

SELECT DISTINCT BRANCH_OFFICE FROM EMPLOYEES

Table 3 BRANCH_OFFICE
        Los Angeles
        Boston
        Chicago

SELECT DISTINCT BRANCH_OFFICE FROM EMPLOYEES
   ORDER BY BRANCH_OFFICE ASC

Table 4 BRANCH_OFFICE
        Boston
        Chicago
        Los Angeles

FIGURE 3: Tables 5 through 7 show the results returned in our examples of the multicolumn SELECT statement.

SELECT BRANCH_OFFICE, FIRST_NAME, LAST_NAME, SALARY, HIRE_DATE
   FROM EMPLOYEES ORDER BY
     SALARY  DESC, HIRE_DATE  DESC

Table 5 

SELECT * FROM EMPLOYEES WHERE LAST_NAME = 'Jones'

Table 6

SELECT * FROM EMPLOYEES WHERE SALARY > 50000

Table 7

Next: Defining Selection Criteria

Published as PC Tech Feature in the 11/17/98 issue of PC Magazine.

Elsewhere on ZDNet
• Learn SQL Online -- ZD University
• Business Software: Databases -- ZD Products