Retrieves rows from the database and allows the selection of one or many rows or columns from one or many tables. This is the primary SQL construct used to express queries. SELECT does not modify, insert, or delete any data.
The main clauses of a SELECT statement are:
SELECT select_list FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ]
A SELECT statement describes a query to the system. The execution of the query does not update any data. The query result is a table with identically structured rows, each of which has the same set of columns. The SELECT statement defines exactly which columns will exist in this result table, and the rows that will populate the table. The SELECT statement does not tell the system how to execute the query; instead, the system executes the query in whatever manner is deemed optimal (using an internal cost-based optimization module). The result is guaranteed to be equivalent to the following canonical execution strategy. The only differences may be in the order of rows in the table, although this will be consistent with any ordering specified by an ORDER BY clause.
- Generate the join of tables in the FROM clause. If the explicit JOIN syntax is used, the JOIN result is obvious. If the FROM clause has a list of table names separated by commas, this is implicitly a cross-product join of the tables.
- If a WHERE clause exists, apply the search condition to the rows resulting from Step 1, and retain only those rows that satisfy the condition.
- If there are no aggregates in the SELECT clause, and if there is no GROUP BY clause, go to Step 7.
- If there is a GROUP BY clause, divide the rows resulting from Step 2 into several groups, such that all the rows in each group have the same value on all the grouping columns. If there is no GROUP BY clause, put all the rows into a single group.
- For each group arising from Step 4, apply the HAVING clause, if it is specified. Only those groups that satisfy the HAVING clause will be retained.
- For each group arising from Step 5, generate exactly one result row by evaluating the select list from the SELECT clause against that group.
- If the SELECT clause has the DISTINCT keyword, eliminate any duplicate rows in the result of Step 6.
- If there is an ORDER BY clause, sort the result of Step 7 as specified by the order expression.