# Databases : PostgreSQL : Queries
> [!NOTE] Order of Operations
> SQL is implemented as if a query was executed in the following order:
> FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
> This order explains which names (columns or aliases) are valid because they must have been introduced in a previous step. ([source](https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by/3841804#3841804))
## Commands
```sql
SELECT *
SELECT <column1> [AS <alias>], ...
SELECT <exp> [AS <alias>], ... -- SELECT 2 + 2 AS total; SELECT max( temp ) FROM weather;
SELECT DISTINCT <column> ... -- return one row for each unique value in column
SELECT DISTINCT <column1>, <column2> ... -- return one row for each unique pair of values
```
```sql
FROM <table>
```
```sql
WHERE <exp>
WHERE <column> = <exp>
```
- comparing columns with `<>` will return `NULL` if either column contains `NULL`
- `IS DISTINCT FROM` will always return `true` or `false`
```sql
ORDER BY <column1> [DESC], ...
ORDER BY 1 [DESC], ... # can use numbers to refer to selected columns by position
```
#### Examples
```sql
-- find rows where values in two columns differ
SELECT foo, bar FROM <table> WHERE foo IS DISTINCT FROM bar
-- compare row counts
SELECT
( SELECT count(*) FROM <table1> ) as foo,
( SELECT count(*) FROM <table2> ) as bar
```
## Operators
In precedence order...
| Operator | Description |
| ------------------------------- | -------------------------------------------------- |
| `.` | table/column name separator |
| `::` | PostgreSQL-style typecast |
| `[ ]` | array element selection |
| `+ -` | unary plus, unary minus |
| `COLLATE` | collation selection |
| `AT` | AT TIME ZONE |
| `^` | exponentiation |
| `* / %` | multiplication, division, modulo |
| `+ -` | addition, subtraction |
| (any other operator) | all other native and user-defined operators |
| `BETWEEN IN LIKE ILIKE SIMILAR` | range containment, set membership, string matching |
| `< > = <= >= <>` | comparison operators |
| `IS ISNULL NOTNULL` | IS TRUE, IS FALSE, IS NULL, IS DISTINCT FROM, etc. |
| `NOT` | logical negation |
| `AND` | logical conjunction |
| `OR` | logical disjunction |
```sql
BETWEEN <v1> AND <v2> -- inclusive; be explicit to avoid overlap: "x >= 50 AND x < 100"
IN ( <value1>, ... )
[I]LIKE <pattern> -- string with wildcards (_, %)
```
Also...
```sql
-- || is a concatenation operator
foo = 'hello' || ' there'
```
## Joins
- inner -> ignores rows that can't be matched
- left / right outer -> includes all rows from specified side regardless of whether there's a match
- full outer -> includes all rows from both sides regardless of whether there's a match
```sql
SELECT * FROM weather JOIN cities ON weather.city = cities.name
SELECT * FROM weather, cities WHERE weather.city = cities.name -- same query, older syntax (less explicit)
JOIN
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
-- self-join
FROM <table> [<alias1>] JOIN <table> [<alias2>] ON ...
```
- You don't need to qualify column (`table.column`) if column is unambiguous across all participating tables.
- It is widely considered good style to qualify all column names in a join query, so that the query won't fail if a duplicate column name is later added to one of the tables.
## EXPLAIN
```sql
EXPLAIN [ANALYZE] [VERBOSE] [FORMAT XML|JSON|YAML] <statement>
# ANALYZE runs command; use transaction to undo results
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
```