Database: SELECT: Syntax

SELECT [DISTINCT] {select-item [AS alias]},...

The select-item can be one of the following:

• A column name from one of the FROM tables, qualified by the table if necessary.
• The star character (*) which selects all columns of a table(s).
• An expression which can be constant ('has a product called'), an arithmetic expression involving literals and columns (sales-quota), a function of a column value(MONTH(hire_date)), summary functions (AVG, MAX, MIN, SUM, COUNT), ...

Example:

SELECT People.first_name, email, (dogs-cats) AS more_dogs

FROM virtual-table

Where virtual-table is

• table-name
• table-name [AS] table-alias -- for self-joins or to save typing
• table-name,... -- SQL1 inner join
  An SQL1 join is partly in the FROM clause (the list of tables) and partly in the WHERE clause (how the joining should be done). If the operator joining the fields is =, it's an inner join. If the operator joining the fields is *=, =*, or *=*, then it's a left, right, or full outer join. If there is no information in the WHERE clause, it's a cross join.
• SQL2-join -- see SQL2 Joins

WHERE condition

The condition can be one of:

• {column | value} {< | <= | = | <> | >= | >} {column | value | [ANY | ALL] (subquery)}.
• column {*= | *=* | =*} column -- SQL1 outer join
• [NOT] BETWEEN low AND hi -- range comparison.
• [NOT] IN (constant,... | subquery) -- set membership.
• LIKE 'pattern' -- pattern matching, % matches 0 or more chars, _ matches exactly one.
• IS [NOT] NULL -- null test.
• condition {AND | OR | NOT | IS [NOT] {TRUE | FALSE | UNKNOWN}} condition
• [NOT] EXISTS (subquery)

GROUP BY grouping-column,...

Typically is used when summary functions are used in the SELECT clause.

HAVING condition

See condition for the WHERE clause. The HAVING conditions are applied to the rows that result from the GROUP BY clause. The condition will always have at least one column function.

ORDER BY {column [DESC]},...

Where column is either a column name or column number. Ascending order is the default; specify DESC for descending order.