...
| Info |
|---|
SQL is a language intended for table transformation. The input is one or more tables, the output is also a table and every instruction applied in the middle gets and outputs a table. Everything in |
...
SELECT clause is about tables. |
Tables have columns and columns have a name and a data type: INT, STRING, DATE, BOOLEAN, etc.
Table rows are data records and the intersection with a column is a value (which can be NULL).
...
The execution order of a SQL query is not the same as the used to write it:
FROM → JOIN ON → WHERE → SELECT → DISCTINCT → GROUP BY → HAVING → ORDER BY → OFFSET → LIMIT
Everything starts with a FROM table.
...
The FROM table (1) is JOIN with other tables ON some constraints to produce a new table.
Then the table (
...
2) above is pruned horizontally with WHERE by dropping all the records
...
not matching the conditions
The new table (
...
3) is pruned vertically then, to match the SELECT columns only.
DISTINCT removes all the duplicate records in (
...
4)
GROUP BY will create a new table from (
...
5) grouping by the
...
provided columns.
HAVING will prune the previous (
...
6) table by removing all the records
...
that don't match the conditions. HAVE acts similarly to the WHERE clause for the GROUP BY.
The filtered grouped table (
...
7) will be ORDER BY some columns
OFFSET N will skip N first records in (
...
8)
and LIMIT M will take the output table from (
...
9) and remove all the records after the M first ones.
The result of all the above operations (many optional) will output another temporary table that will be destroyed once it has been read.
...