The SELECT statement retrieves data from a database and returns it to you in the form of query results. You have already seen many examples of the SELECT statement in the quick tour presented in Chapter 2. Here are several more sample queries that retrieve information about sales offices:
List the sales offices with their targets and actual sales.
List the Eastern region sales offices with their targets and sales.
List Eastern region sales offices whose sales exceed their targets, sorted in alphabetical order by city.
What are the average target and sales for Eastern region offices?
For simple queries, the English language request and the SQL SELECT statement are very similar. When the requests become more complex, more features of the SELECT statement must be used to specify the query precisely.
Figure 6-1 shows the full form of the SELECT statement, which consists of six clauses. The SELECT and FROM clauses of the statement are required. The remaining four clauses are optional. You include them in a SELECT statement only when you want to use the functions they provide. The following list summarizes the function of each clause:
- The SELECT clause lists the data items to be retrieved by the SELECT The items may be columns from the database, or columns to be calculated by SQL as it performs the query. The SELECT clause is described in the next section.
- The FROM clause lists the tables that contain the data to be retrieved by the query. Queries that draw their data from a single table are described in this chapter. More complex queries that combine data from two or more tables are discussed in Chapter 7.
- The WHERE clause tells SQL to include only certain rows of data in the query results. A search condition is used to specify the desired rows. The basic uses of the WHERE clause are described in the “Row Selection (WHERE Clause)” section later in this chapter. Those that involve subqueries are discussed in Chapter 9.
- The GROUP BY clause specifies a summary query. Instead of producing one row of query results for each row of data in the database, a summary query groups together similar rows and then produces one summary row of query results for each group. Summary queries are described in Chapter 8.
- The HAVING clause tells SQL to include only certain groups produced by the GROUP BY clause in the query results. Like the WHERE clause, it uses a search condition to specify the desired groups. The HAVING clause is described in Chapter 8.
- The ORDER BY clause sorts the query results based on the data in one or more columns. If it is omitted, the query results are not sorted. The ORDER BY clause is described in the “Sorting Query Results (ORDER BY Clause)” section later in this chapter.
1. The select Clause
The SELECT clause that begins each SELECT statement specifies the data items to be retrieved by the query. The items are usually specified by a select list, a list of select items separated by commas. Each select item in the list generates a single column of query results, in left-to-right order. A select item can be one of the following:
- A column name, identifying a column from the table(s) named in the FROM When a column name appears as a select item, SQL simply takes the value of that column from each row of the database table and places it in the corresponding row of query results.
- A constant, specifying that the same constant value is to appear in every row of the query results.
- A SQL expression, indicating that SQL must calculate the value to be placed into the query results, in the style specified by the expression.
Each type of select item is described later in this chapter.
2. The from Clause
The FROM clause consists of the keyword FROM, followed by a list of table specifications separated by commas. Each table specification identifies a table containing data to be retrieved by the query. These tables are called the source tables of the query (and of the SELECT statement) because they are the source of all of the data in the query results.
All of the queries in this chapter have a single source table, and every FROM clause contains a single table name.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.