SQL Quick tutorial

Owned by Pablo Beltran
Last updated: Nov 18, 2022 by Mariia Romanova
2 min read

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

 

  1. Everything starts with a FROM table.

  2. The FROM table (1) is JOIN with other tables ON some constraints to produce a new table.

  3. Then the table (2) above is pruned horizontally with WHERE by dropping all the records not matching the conditions

  4. The new table (3) is pruned vertically then, to match the SELECT columns only.

  5. DISTINCT removes all the duplicate records in (4)

  6. GROUP BY will create a new table from (5) grouping by the provided columns.

  7. 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.

  8. The filtered grouped table (7) will be ORDER BY some columns

  9. OFFSET N will skip N first records in (8)

  10. 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.