SELECT statement retrieves rows from the database.
-- select all columns from the table "tbl" SELECT * FROM tbl; -- select the rows from tbl SELECT j FROM tbl WHERE i=3; -- perform an aggregate grouped by the column "i" SELECT i, SUM(j) FROM tbl GROUP BY i; -- select only the top 3 rows from the tbl SELECT * FROM tbl ORDER BY i DESC LIMIT 3; -- join two tables together using the USING clause SELECT * FROM t1 JOIN t2 USING(a, b);
SELECT statement retrieves rows from the database. The canonical order of a select statement is as follows, with less common clauses being indented:
SELECT select_list FROM tables USING SAMPLE sample_expr WHERE condition GROUP BY groups HAVING group_filter WINDOW window_expr QUALIFY qualify_filter ORDER BY order_expr LIMIT n
SELECT statement can be prefixed with a
SELECT statement is so complex, we have split up the syntax diagrams into several parts. The full syntax diagram can be found at the bottom of the page.
SELECT clause specifies the list of columns that will be returned by the query. While it appears first in the clause, logically the expressions here are executed only at the end. The
SELECT clause can contain arbitrary expressions that transform the output, as well as aggregates and window functions.
FROM clause specifies the source of the data on which the remainder of the query should operate. Logically, the
FROM clause is where the query starts execution. The
FROM clause can contain a single table, a combination of multiple tables that are joined together, or another
SELECT query inside a subquery node.
SAMPLE clause allows you to run the query on a sample from the base table. This can significantly speed up processing of queries, at the expense of accuracy in the result. Samples can also be used to quickly see a snapshot of the data when exploring a data set. The sample clause is applied right after anything in the
from clause (i.e. after any joins, but before the where clause or any aggregates). See the sample page for more information.
WHERE clause specifies any filters to apply to the data. This allows you to select only a subset of the data in which you are interested. Logically the
WHERE clause is applied immediately after the
GROUP BY/HAVING clause
GROUP BY clause specifies which grouping columns should be used to perform any aggregations in the
SELECT clause. If the
GROUP BY clause is specified, the query is always an aggregate query, even if no aggregations are present in the
WINDOW clause allows you to specify named windows that can be used within window functions. These are useful when you have multiple window functions, as they allow you to avoid repeating the same window clause.
QUALIFY clause is used to filter the result of
ORDER BY/LIMIT clause
ORDER BY and
LIMIT are output modifiers. Logically they are applied at the very end of the query. The
LIMIT clause restricts the amount of rows fetched, and the
ORDER BY clause sorts the rows on the sorting criteria in either ascending or descending order.
VALUES list is a set of values that is supplied instead of a
For each table, the
rowid pseudocolumn returns the row identifiers based on the physical storage.
D CREATE TABLE t(id int, content string); D INSERT INTO t VALUES (42, 'hello'), (43, 'world'); D SELECT rowid, id, content FROM t; ┌───────┬────┬─────────┐ │ rowid │ id │ content │ ├───────┼────┼─────────┤ │ 0 │ 42 │ hello │ │ 1 │ 43 │ world │ └───────┴────┴─────────┘
In the current storage, these identifiers are contiguous unsigned integers (0, 1, …) if no rows were deleted. Deletions introduce gaps in the rowids which may be reclaimed later. Therefore, it is strongly recommended not to use rowids as identifiers.
rowidvalues are stable within a transaction.
If there is a user-defined column named
rowid, it shadows the
Common Table Expressions
Full Syntax Diagram
Below is the full syntax diagram of the