SQL
The SQL language has several parts:
- Data-definition language (DDL). The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.
- Data-manipulation language (DML). The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database.
Logical Query Processing Order
FROM and JOIN
| Step | Description |
|---|---|
| 1 | Cartesian product |
| 2 | Qualification step (i.e. the ON filter) |
| 3 | LEFT or RIGHT reservations |
If the specified JOIN type is a CROSS JOIN, then the query planner stops at Step
1 and return the cartesian product to the next clause. All other join types
proceed to the qualification step.
INNER and OUTER JOINS are called "qualified JOINs", the qualification
predicated is specified using the ON keyword, which filters all rows from the
previous cartesian step where the predicate is TRUE.
If the request is an INNER JOIN, then the qualified rows are returned to the
next clause, if it is an OUTER JOIN, the qualified rows go throught the next
step.
Chiastic Order
WHERE
GROUP BY
Stream/Group Aggregation
- Requires input streams to be sorted.
- Useful for "Top-N" queries, as it is more memory efficient due to its streaming behaviour.
- It's called
GroupAggregateon theEXPLAIN ANALYZEoutput.
Hash-based Aggregation
- Builds a temporary in-memory hash table using the conditions from
GROUP BY. - Does not require the input stream to be sorted.
- It's called
HashAggregateon theEXPLAIN ANALYZEoutput.
HAVING
- Acts as a
WHEREclause on the outputs generated by theGROUP BY.
Window Functions
SELECT
DISTINCT
- Can either happen via a
HashAggregateor a combination ofSORT/UNIQUE.