SQL SELECT
Internal
Overview
A query consists in at least one (SELECT) and at most six categories of clauses:
SELECT [one or more things] FROM [one or more places] WHERE [one or more conditions apply] GROUP BY [...] HAVING [...] ORDER BY [...];
Almost every query will include at least three of these clauses (SELECT, FROM and WHERE).
Once submitted to the server, the server verifies the syntax and passes the query to the query optimizer to be executed. The optimizer look at such things as the order in which to join tables, what indexes are available, etc. and the picks an execution plan.
The execution consists in selecting all rows, possibly across multiple tables, then filtering and discarding the rows that do not match the filter conditions specified in the WHERE clause, if present.
Upon execution, an SQL query returns a result set, which is just another table containing rows and columns.
Example
SELECT id, name FROM person WHERE id = 1;
The following query:
SELECT;
is valid, it returns one empty row.
Clauses
SELECT
SELECT [one or more things] ...
SELECT determines which columns from the FROM sources need to be retrieved and included in the result set. Even though SELECT clause is the first in the statement, it is one of the last clauses to be evaluated. This is because before the server can determine what to include in the final result set, it needs to know all of the possible columns that could be included, which are determined by the content of the FROM clause. A very obvious example is SELECT * FROM person;: only the person table columns can be included.
All columns can be specified with an *:
SELECT * FROM person
Specific columns can be named:
SELECT id, name FROM person
Aside from column names, SELECT accepts literals, such as numbers or strings, expressions, built-in function calls and user-defined function calls. In each of these cases, the label of the "synthetic" column such generated can be specified after the literal/expression/function. These labels are known as column aliases:
/* prefix, shifted_id and upper_name are column aliases */
SELECT name original_name, '@@@' some_prefix, id + 5 shifted_id, UPPER(name) upper_name FROM person
original_name | some_prefix | shifted_id | upper_name ---------------+-------------+------------+-------------- Binh Ngo Jr. | @@@ | 6 | BINH NGO JR.
In order to make your column aliases stand out even more, the AS keyword can be optionally used before the alias name:
SELECT name AS original_name, '@@@' AS some_prefix, id + 5 AS shifted_id, UPPER(name) AS upper_name FROM person
DISTINCT
The DISTINCT keyword specified after SELECT removes duplicates.
SELECT DISTINCT eye_color FROM person;
Note that generating a distinct set of results requires data to be sorted, which may have performance implication for large result sets.
Analyze situations. What if there are multiple columns?
FROM
... FROM [one or more places] ...
FROM identifies the tables from which to retrieve data, and, if there are more than one tables, how the tables should be joined. The most common situation involves just one table:
SELECT p.name FROM person AS p;
Each of the tables provided in the FROM clause can be renamed within the context of the query with a table alias, introduced by the keyword AS. The table aliases can then also be used to prefix the column names used by SELECT clause. This feature is useful to disambiguate between columns with the same name in different tables.
The tables are not necessarily permanent tables, they can also be derived, temporary or virtual (views). When a query is issued against a view, the query is merged with the view definition to create a final query to be executed.
Querying a Derived Table
Derived tables are dynamically generated in memory by subqueries, which are complete SELECT statements declared inside the FROM clause of a main query (or containing query). These tables are then "queried" by the main query as it were regular tables:
SELECT specific_person.name FROM
(
SELECT id, name, birthday FROM person WHERE id = 1
) AS specific_person;
Every time a subquery is used, the subquery must be identified with an alias, prefixed by the optional keyword AS.
Querying Multiple Tables
WHERE
GROUP BY ... HAVING
ORDER BY
ORDER BY clause sorts the rows of the final result set by one or more columns.