Skip to main content

SELECT

Retrieve rows of data from zero or more tables.

caution

Non-standard tables in the MySQL data source information_schema cannot be queried with the SELECT statement. (TABLE NOT FOUND)
For information on non-standard tables, please refer to the MySQL official documentation.

Overview​

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] SELECT ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ OFFSET count [ ROW | ROWS ] ]
[ LIMIT { count | ALL } ]

Parameters​

[ WITH with_query [, ...] ]​
  • The WITH clause allows defining and using subqueries within a query. It can remove or simplify nested queries.
SELECT [ ALL | DISTINCT ] select_expression [, ...]​
  • The SELECT clause determines the output of the query. Using ALL includes all rows, DISTINCT returns only unique rows.
  • The default behavior is ALL.
[ FROM from_item [, ...] ]​
  • The FROM clause defines the data source to retrieve data rows.
  • from_item can be in the following forms:
table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
from_item join_type from_item
  [ ON join_condition | USING ( join_column [, ...] ) ]

  • join_type can be:

    • [ INNER ] JOIN
    • LEFT [ OUTER ] JOIN
    • RIGHT [ OUTER ] JOIN
    • FULL [ OUTER ] JOIN
    • CROSS JOIN
[ WHERE condition ]​
  • The WHERE clause filters output rows according to the specified condition.
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]​
  • The GROUP BY clause groups results according to values of specific columns. It allows aggregation functions to apply on grouped rows.
  • grouping_element can be:
GROUPING SETS ( ( column [, ...] ) [, ...] )
CUBE ( column [, ...] )
ROLLUP ( column [, ...] )
  • GROUPING SETS ( ( column [, ...] ) [, ...] ): Allows grouping data in various ways. Multiple aggregation results can be obtained with a single query. Columns not included in grouping are set to NULL.
  • CUBE ( column [, ...] ): Enables multidimensional aggregation, generating all combinations of groups. If a value for a combination is missing, it is represented as NULL.
  • ROLLUP ( column [, ...] ): The ROLLUP operator generates all possible subtotals for the given set of columns.
[ HAVING condition ]​
  • The HAVING clause applies conditions on aggregate function results. It filters grouped data.
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] SELECT ]​
  • Set operations combine results of SELECT statements into a new result set. By default, duplicate rows are removed.
    • UNION: Combines results of two or more SELECT statements into one result set.
    • INTERSECT: Creates a new result set containing only rows common to two or more SELECT statements.
    • EXCEPT: Creates a new result set containing rows from the first SELECT statement that are not in the second.
  • Unless parentheses specify order, they are processed left to right. INTERSECT binds more tightly than EXCEPT and UNION.
[ ORDER BY expression [ ASC | DESC ] [, ...] ]​
  • Used to sort query results by the values of specified columns.
[ OFFSET count [ ROW | ROWS ] ]​
  • The OFFSET clause excludes the specified number of leading rows from the results.
[ LIMIT { count | ALL } ]​
  • Limits the number of rows returned by the query.