Commands

Commons (Data Manipulation)

SELECT

SELECT [ DISTINCT [ ON ( expression [,...] ) ] | ALL ]
selectExpression [,...]
[ FROM tableExpression [,...] ]
[ WHERE expression ]
[ GROUP BY groupingElement [,...] ] [ HAVING expression ]
[ WINDOW { { windowName AS windowSpecification } [,...] } ]
[ QUALIFY expression ]
[ { UNION [ ALL ] | EXCEPT | INTERSECT } query ]
[ ORDER BY selectOrder [,...] ]
[ OFFSET expression { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ expression [ PERCENT ] ] { ROW | ROWS }
{ ONLY | WITH TIES } ]
[ FOR UPDATE ]

Selects data from a table or multiple tables.

Command is executed in the following logical order:

1. Data is taken from a table value expressions that are specified in the FROM clause, joins are executed. If FROM clause is not specified a single row is constructed.

2. WHERE filters rows. Aggregate or window functions are not allowed in this clause.

3. GROUP BY groups the result by the given expression(s). if GROUP BY clause is not specified, but non-window aggregate function are used or HAVING is specified all rows are grouped together.

4. Aggregate functions are evaluated.

5. Having filters rows after grouping and evaluation of aggregate functions. Non-window aggregate functions are allowed in this clause.

6. Window functions are evaluated.

7. QUALIFY filters rows after evaluation of window functions. Aggregate and window functions are allowed in this clause. (Non-standard syntax)

8. DISTINCT removes duplicates. If DISTINCT is used only the specified expressions are checked for duplicated; ORDER BY caluse, if any, is used to datetime preserved rows. First row is each DISTINCT ON group is preserved. In absence of ORDER BY preserved rows are not datermined, database may choose any row from each DISTINCT ON group.

9. UNION, EXCEPT and INTERSECT combine the result of this query with the results of another query. INTERSECT has higher precedence than UNION and EXCEPT. Operators with equal precedence are evaluated from left to right.

10. ORDER BY sorts the result by the given column(s) or expression(s).

11. Number of rows in output can be limited with OFFSET and FETCH clauses. OFFSET specifies how many rows to skip. Please note that queries with high offset values can be slow. FETCH FIRST/NEXT limits the number of rows returned by the query. If PERCENT is specified number of rows is specified as a percent of the total number of rows and should be an integer value between 0 and 100 inclusive. WITH TIES can be used only together with ORDER BY and means that all additional rows that have the same sorting position as the last row will be also returned.

Example:

SELECT * FROM TEST;
SELECT * FROM TEST ORDER BY NAME;
SELECT ID, COUNT(*) FROM TEST GROUP BY ID;
SELECT NAME, COUNT(*) FROM TEST GROUP BY NAME HAVING COUNT(*) > 2;
SELECT 'ID' COL, MAX(ID) AS MAX FROM TEST UNION SELECT 'NAME', MAX(NAME) FROM TEST;
SELECT * FROM TEST OFFSET 1000 ROWS FETCH FIRST 1000 ROWS ONLY;
SELECT A, B FROM TEST ORDER BY A FETCH FIRST 10 ROWS WITH TIES;
SELECT * FROM (SELECT ID, COUNT(*) FROM TEST
    GROUP BY ID UNION SELECT NULL, COUNT(*) FROM TEST)
    ORDER BY 1 NULLS LAST;
SELECT DISTINCT C1, C2 FROM TEST;
SELECT DISTINCT ON(C1) C1, C2 FROM TEST ORDER BY C1;