SELECT The SELECT statement fetches data from a database. It has this basic syntax:

Where:

1. DISTINCT eliminates duplicates from the resultset.
2. expression is a valid column name, function or calculation. (*, which may take the place of expression, requests all columns from all tables that the FROM clause specifies.)
3. output_name is the name (i.e. the column heading) for expression in the resultset.
4. from_item is a table name.
5. select_statement is a subquery. For more on subqueries, see Chapter 5.
6. alias is an alias. Every table name or subquery in the from_item can have its own alias, which then becomes the reference to the item in the rest (with some exceptions) of the SELECT statement.
7. join_type is a valid JOIN type. For more on JOIN types, see Chapter 3.
8. join_condition is an expression that evaluates to true or false, specifying which rows in the joined tables match. Typically, this takes the form table1.field_value = table2.field_value.
9. condition is an expression that evaluates to true or false.
10. grouping_element is a column name or column names that provide values to aggregate.
11. order_expr is a column name or set of names, or a function or expression with an orderable result, e.g. concat(last_name, ', ' first_name).

Each expression may optionally specify an output_name, which specifies the column heading in the resultset. If no output name is assigned, the name of the column in the table, or the function if the column is the result of a function call, or the string ?column? otherwise, becomes the column heading.

SELECT with Scalar Queries

In its simplest form, SELECT returns the scalar result of a simple calculation or a function call. For example, this SQL statement:

Has this result:

SELECT can also call various functions. For example, SELECT now(); returns the current date and time. Available functions vary from one RDBMS to another.

SELECT Clauses

1. The FROM clause defines the set of data from which the resultset will be selected. If the FROM clause is used, at least one from_item is required.
2. The JOIN clause is a subclause of the FROM clause. By joining tables together, the clause expands the set of data from which the resultset will be selected.
3. The WHERE clause eliminates from the output all rows that do not satisfy the condition(s) given in condition.
4. The GROUP BY clause combines the output into groups of rows that match on the values specified in grouping_element. The results of aggregate functions are computed against each such group.
5. The HAVING clause eliminates groups specified in the GROUP BY clause that do not satisfy the condition(s) given in condition.
6. The ORDER BY clause sorts the resultset as specified in expression, in either ascending or descending order.

Query Plan Order

SELECT SELECT queries run in roughly in the order of the SELECT statement syntax.

1. Get all the rows in the FROM clause.
2. Filter rows based on WHERE clause.
3. Group based on GROUP BY clause. Calculate aggregate functions.
4. Filter groups based on HAVING clause.
5. Apply any calculations specified in the SELECT list, filter duplicates if DISTINCT is specified, and eliminate any columns not specified in the column list.
6. Sort based on ORDER BY clause.

Most execution plans use this order, but there are exceptions.

The next chapters will cover the SELECT clauses in more detail.