Basic SQL Query
Somebody suggested that I post a simple quick view of how a basic query works. The following illustrates how the FROM
is read first. Aliases assigned in the FROM
clause, like the i
, replace the full name of the table or view. Column references don’t require prepending with the table or view alias but doing so adds clarity in the query. You do need prepend table aliases or names when two or more columns returned by the query have the same names. This happens when you’re joining two or more tables because the SQL parser finds that they’re ambiguously defined otherwise.
While the column aliases use the optional AS
keyword, table aliases must directly follow the table name. Table name really means a table, view, or inline view name. They can also mean a subquery factoring clause, which is the fancy name for a WITH
statement – blogged on it here.
Selection comes in two phases, identifying the sources or tables in the FROM
clause, and then filtering the sources based on comparisons in the WHERE
clause. Join statements are also filters that match rows from different tables based on value or range comparisons. You find join statements in the FROM
clause as part of a ON
or USING
subclause when queries use key words like JOIN
et cetera. You find join statements in the WHERE
clause when the queries list tables as comma separated elements in a FROM
clause.
Projection is the narrowing of rows into columns qualified by the select list. A select list is the comma separated columns returned by a query in the SELECT
clause.
While database management systems have their own particulars about sequencing and optimization, more or less they find the data sources, read the rows or indexes to rows, and then narrow the columns returned to those qualified in a select list. That’s about it unless a query involves aggregation or sorting operations.
Aggregation typically happens after selection but before projection. While columns in the SELECT
clause often set the aggregation grouping, you may use columns other than those in the select list. You qualify the grouping columns in the optional GROUP BY
clause.
Sorting by a column is done through the ORDER BY
clause. The sorting of data follows the selection process, unless there is an aggregation process. Sorting follows aggregation when it is present in a query. Aggregated data sets are limited to ordering by columns in the GROUP BY
clause.