SQL, Chapter 2: the SELECT Statement

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

SELECT [ DISTINCT ]
  [ * | expression [ [ AS ] output_name ] [, ...] ]
  [ FROM from_item [ [ AS ] alias ] [, ...] ]
  [{ join_type from_item ON join_expression ] [...] | CROSS JOIN from_item }]
  [ WHERE condition ]
  [ GROUP BY grouping_element [, ...] ]
  [ HAVING condition [, ...] ]
  [ ORDER BY order_expr [ ASC | DESC ] [, ...] ]        

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 or a parenthesized subquery.
5. 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.
6. join_type is a valid JOIN type: [ INNER ] JOIN, LEFT [ OUTER ] JOIN, RIGHT [ OUTER ] JOIN or FULL [ OUTER ] JOIN.
7. 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.
8. condition is an expression that evaluates to true or false.
9. grouping_element is a column name or column names that provide values to aggregate.
10. 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).
11. count and start are integers.

Each expression may optionally specify an output_name, which will display as a column heading in the resultset. If no 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:

SELECT 2 + 4 AS sum;        

Has this result:

sum

6

SELECT can also call various functions. For example, SELECT now(); returns the current date and time. The 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 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.

I have been writing these articles on my "blogfolio" first, and then transferring them over to LinkedIn. If I haven't put the next chapter up here yet, the link to the next chapter will take you to the copy on robertrodes.com.

Chapter 1: Overview

Chapter 3: the FROM Clause and JOIN Subclause