> For the complete documentation index, see [llms.txt](https://ciusji.gitbook.io/guinsoo/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://ciusji.gitbook.io/guinsoo/reference/commands.md).

# Commands

### Commons (Data Manipulation)

#### SELECT

{% hint style="info" %}

```
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 ]
```

{% endhint %}

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;
```