Select – performs a selection (extraction of individual rows) and/or a projection (extraction of individual columns) of the data and returns the result set.
Simple option:
1 2 | SELECT [ALL | DISTINCT] column_list FROM {table1 [tab_aliasl]},... |
Parameters:
- table1 – selection table name
- tab_alias1 – table alias (alternative abbreviated table name)
- column_list could be:
- asterisk symbol (*) – all columns
- explicit column names
- name and alias: column_name [AS] column_heading
- expression
- system or aggregate function
- ALL | DISTINCT
- ALL (default) – all rows, including duplicates
- DISTINCT – only unique projection rows (allows you to exclude duplicate row values, while the entire projection rows, not individual columns, are considered duplicates)
Full syntax:
1 2 3 4 5 6 7 | SELECT select_list [INTO new_table] FROM table [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC]]; |
Offers must be in the order listed.
The WHERE clause is a selection of rows. Must contain a boolean expression that is tested for each row. Any equality and comparison operators can be used:
- = equal
- <> (or !=) is not equal
- < less than
- > more than
- >= greater than or equal
- <= less than or equal to
- !> no more than
- !< not less than
An expression can be part of the condition in the WHERE clause.
Conditions can be compound (contain several conditions). Such conditions are created using the logical operators AND, OR and NOT. When conditions are combined with the AND operator, only rows that satisfy both conditions are returned. When two conditions are joined with the OR operator, all rows in the table that satisfy one or both of these conditions are returned. NOT reverses the boolean value. Operators have different precedence (NOT is the highest, OR is the lowest), you can change it with parentheses.
The IN operator allows you to determine whether the value of the first operand (for example, the value of a column) matches one of the values from an arbitrary list in the second operand (the operator is equivalent to a sequence of conditions connected by the OR operator):
1 | WHERE test_column [NOT] IN (value1, value2,…) |
Can be used with the NOT operator.
The BETWEEN operator allows you to determine whether the value of the first operand is within the range specified by the second operand (can be replaced by two comparisons combined with the AND operator):
1 | WHERE test_column [NOT] BETWEEN low_value AND high_value |
None of the listed operators can select values equal or non-NULL, because any comparisons with NULL always return FALSE (NULL is not even equal to itself). The IS [NOT] NULL operator is used to select rows that contain or do not contain a NULL value.
The LIKE operator is used for pattern matching. It only applies to string data types and dates:
1 | column [NOT] LIKE 'pattern' |
You can use the following special characters in the pattern:
- % (percent sign) – denotes a sequence of any characters of any length
- _ (underscore) – stands for any single character
- [] – set character range
- ^ – negate a range of characters
To escape special characters, you can insert them in square brackets or use the ESCAPE operator.
The ORDER BY clause specifies the sort order of the result set:
1 | ORDER BY {[col_name | col_number [ASC | DESC]]} , ... |
The OFFSET clause specifies the number of result rows to skip in the displayed result. This number is calculated after the rows are sorted by the ORDER BY clause.
The FETCH NEXT clause specifies the number of matching WHERE and sorted rows to return.