SELECT : The most used statement
The SELECT statement is the most frequently used statement in SQL, it is used to retrieve data from one or more tables. The names of the tables used in the statement must be listed in the statement. If a column-name appears in more than one table, references to the column must be written as table.column to avoid ambiguity. The syntax for SELECT is used in UPDATE and DELETE, for example:
SELECT name,pay FROM payroll WHERE name = 'poor man' ; UPDATE payroll SET pay = pay * 10 WHERE name = 'poor man' ; DELETE FROM payroll WHERE pay < 0 ; |
The railway diagram shows the SQL grammar for select with keywords in uppercase, branches show alternatives in the grammar and loops show repetition. Note that a select statement can contain several sub-selections.
Notes about SELECT
- ORDER BY
The result of a select statement is not sorted unless ORDER BY is used, when the column(s) to be used for sorting are given. Ascending order (ASC) is the default, specify DESC for descending order. For example to list films longest first:
SELECT title,length FROM films ORDER BY length DESC ;- DISTINCT
Duplicate rows in the result of SELECT are all shown unless DISTINCT is used. For example to remove duplicate titles:
SELECT DISTINCT title FROM Films ;- Alias
Tables or columns may have an alias to shorten queries or let the same table to be used several times in a single query. Here A is used as a shorthand for the table actor
SELECT COUNT(A.name) FROM actors A WHERE A.name < 'Zorro' ;- GROUP BY
rearranges the original table into logical partitions, in each partition all rows have the same value in the specified column(s). For example to group films by director and give the size of each group:
SELECT director,COUNT(director) FROM films GROUP BY director;- HAVING
selects groups from the partitions generated by GROUP BY. For example:
SELECT part FROM casting GROUP BY part HAVING part > "A" ;- Predicates
evaluate to TRUE, FALSE or UNKNOWN. Comparison operations work on dates and times as well as numbers and strings. UNKNOWN is returned if one the operands is NULL.
- A pattern
is a string constant that may contain wild-card characters: `%' matches any number of characters, `_' (underscore) matches a single character for example to select any actors name containing o.
SELECT name FROM actors WHERE name LIKE '%o%';- A set
is either a bracketed list of expressions, or a nested SELECT query. The ability to nest queries gives SELECT much of its power, for example to look for actor-directors:
SELECT director FROM films /* Any actor-directors ? */ WHERE director IN (SELECT name FROM actors );- EXISTS
is TRUE if the nested query returns any data, for example:
SELECT director FROM films WHERE EXISTS ( SELECT name FROM actors WHERE born < '1-jan-1900') ;