ConceptDBISAI-written · not yet proofread

SQL Query Structure

A SQL SELECT query consists of the clauses SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT, whose result is defined by evaluating them in a fixed logical order starting from the Cartesian product of the FROM tables.

Know these firstRelational Model

Definition

A SQL SELECT query has the form

SELECT   [DISTINCT] columns / aggregates     -- comma-separated
FROM     table1, table2, ...                 -- or table1 JOIN table2 ON ...
WHERE    Boolean expression on columns
GROUP BY grouping_columns
HAVING   Boolean expression on aggregates
ORDER BY sort_expression [ASC | DESC]
LIMIT    n [OFFSET m];

Only SELECT and FROM are mandatory, and the clauses must appear in this order. Its result is the table obtained by evaluating the clauses in the logical order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/OFFSET. Columns are written <table>.<column> when the name is ambiguous, and AS gives a table or result column a new name.

Properties#

Clause semantics#

Clause	Stage	What it does	Operation
`FROM`	1	the Cartesian Product of all listed tables (or their Join with `JOIN … ON`)	$\times$ , $\bowtie$
`WHERE`	2	keeps the rows for which the expression is true	selection $\sigma$
`GROUP BY`	3	partitions the surviving rows into groups and aggregates per group	Grouping
`HAVING`	4	keeps the groups whose aggregated row satisfies the condition	filter after group-by
`SELECT`	5	keeps the listed columns / computes aggregates; `DISTINCT` removes duplicates	projection $\Pi$
`ORDER BY`	6	sorts the final result	Ordering
`LIMIT`/`OFFSET`	7	keeps a segment of the sorted result	Ordering

For the basic form SELECT … FROM … WHERE …: the result is all rows of the Cartesian product of the FROM tables for which the WHERE expression is true, restricted to the SELECT columns. In the operations of Relational Algebra: $\Pi_{\mathit{cols}}(\sigma_\varphi(T_1 \times \dots \times T_k))$ .

Aggregation functions#

Function	Value per group
`COUNT(*)`	number of rows
`COUNT(col)`	number of non-NULL values of `col`
`SUM(col)`, `AVG(col)`	sum, arithmetic mean
`MIN(col)`, `MAX(col)`	minimum, maximum
`GROUP_CONCAT(col, sep)`	values concatenated with separator `sep` (SQLite3)

Without GROUP BY, an aggregate is computed over all rows of the (filtered) table.

JOIN variants#

See Join for the definitions.

Implicit join: FROM A, B WHERE A.x = B.y (traditional).
Explicit join: FROM A JOIN B ON A.x = B.y (most modern); LEFT JOIN / RIGHT JOIN keep unmatched rows of one side, filled with NULL.
NATURAL JOIN: equi-join on all columns that have the same name in both tables; each such column appears once in the output.

Subqueries#

Wherever SQL expects a table, a whole SELECT query in parentheses may be written (Subquery).

Relation to SPARQL#

Basic SPARQL queries translate mechanically into SELECT … FROM … WHERE … queries: one table alias per query triple, equality conditions for constants and shared variables (SPARQL to SQL Translation).

Example#

ExampleRoles of all actors

Schema: movies(id, title, year, score), persons(id, name, birth_date), roles(movie_id, person_id).

SELECT movies.title, persons.name
FROM movies, persons, roles
WHERE roles.person_id = persons.id
  AND roles.movie_id = movies.id;

FROM forms all $|R_\text{movies}| \cdot |R_\text{persons}| \cdot |R_\text{roles}|$ combinations, WHERE keeps exactly one row per role (the matching movie and person), SELECT keeps title and name: (The Shawshank Redemption, Morgan Freeman), (The Dark Knight, Morgan Freeman), (The Dark Knight, Maggie Gyllenhaal).

ExampleAll clauses (WS 23/24, Task 2.1)

Top 10 movies by total views on Netflix and Amazon Prime:

SELECT movies.title, SUM(streaming.views) AS total_views
FROM movies, streaming
WHERE movies.id = streaming.movie_id
  AND streaming.platform IN ('Netflix', 'Amazon Prime')
GROUP BY movies.id
ORDER BY total_views DESC
LIMIT 10;

Join via WHERE, filter rows, one group per movie, SUM per group, sort by the aggregate, keep the first 10.

Sources#

Slides 03, p. 28–38 (basic query, FROM/WHERE/SELECT semantics)
Slides 04, p. 15 (translation into operations)
Slides 05, p. 21 (JOIN … ON, NATURAL JOIN), p. 26–27 (HAVING), p. 30–34 (ORDER BY, LIMIT/OFFSET, subqueries)
DBIS_VL03 > SQL Basics, DBIS_VL03 > FROM: the cross-product, DBIS_VL03 > WHERE: filtering the cross-product, DBIS_VL03 > SELECT: choosing columns, DBIS_VL05 > More join operations, DBIS_VL05 > Grouping, DBIS_VL05 > More SQL, DBIS_VL07 > Differences between SPARQL and SQL
Exams: DBIS Exam WS2324 (not published) (Tasks 2.1, 3.3), DBIS Exam WS2324-2 (not published) (Tasks 2.1, 3.3), DBIS Exam WS1617 (not published) (Task 6.2)

Connections

Prerequisites know first1

Relational Model

Mentioned in inline links8

Cartesian Product
SQL: the FROM clause with several comma-separated tables denotes their Cartesian product; WHERE then selects rows of it (SQL Query Structure). Without a WHERE clause the full product really must be computed. §
Ordering
Clause order: GROUP BY → ORDER BY → LIMIT / OFFSET; in the logical evaluation order, ordering comes after SELECT (so it may use result aliases such as total_views) and before LIMIT (SQL Query Structure). §
SPARQL Query Structure
Differences to SQL: §
SPARQL to SQL Translation
Let the triples be stored in one table triples(subject, predicate, object) and let SELECT ?v1 … ?vs WHERE { T1 . … . Tk } be a basic SPARQL query with $k$ query triples. The equivalent SQL query is built as follows: §
VL03 – Database Basics
Basic SQL query (SQL Query Structure) §
The scratchpad’s SQL Query Structure note already lists the complete clause order SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, aggregation functions (COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT) and NATURAL JOIN. These are not… §
VL05 – Joins and more SQL
(Slide 30) The SQL standard (ISO/IEC 9075:2023) has hundreds of pages; the course covers only the most important operations (see https://sqlite.org/lang.html). The following three are explained by example only, without formal operations. A… §
SQL Query Structure — JOIN … ON, GROUP BY/HAVING, ORDER BY, LIMIT/OFFSET and subqueries in a SELECT query §
VL07 – Knowledge Graphs, SPARQL, SPARQL to SQL
where the sum is over all variables $v$ of the query and $m_v$ is the number of occurrences of $v$ in the query triples (SPARQL to SQL Translation). Each join equality is an equi-Join condition in the sense of SQL’s FROM/WHERE. §
SQL Query Structure — target of the translation (SELECT/FROM/WHERE with aliases) §
SQL and Query Planning
SQL Query Structure — clauses SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT and their evaluation order §

Search in these mentions →