Structured Query Language (SQL) Knowledge
Kip Landergren
(Updated: )
My Structured Query Language (SQL) knowledge base explaining an overview and some statements.
Contents#
Overview#
Structured Query Language (SQL) is a declarative—meaning you state what you want, not how to get it—language for querying and manipulating data from a database. SQL operators are compositional around relations, meaning that they take relations as input and return relations as outputs. This allows for expressivity when creating queries.
Core Idea#
Use declarative statements to perform operations on relations that will return relations. Understand that there will be many equivalent statements, and the performance of each can be examined by working with the query planner.
Key Concepts#
- relational algebra
- set theory
- logic theory
Terminology#
- aggregate function#
- 1.
Reduces multiple inputs to a single output value; computes a single result from multiple input rows.
Related:
- alias#
- 1.
A temporary name assigned to a table or column.
- column reference#
- 1.
Correlation.columnname.
Related:
- command#
- 1.
- dangling tuple#
- 1.
Tuple from a join that does not have a match.
Related:
- delimited identifier#
- 1.
A sequence of characters enclosed by double quotes and treated as case sensitive; is always considered an identifier and never a key word.
Also: quoted identifier
Broader:
- execution plan#
- 1.
The actual sequence of steps necessary to execute the SQL statement.
Related:
- FROM#
- 1.
Specifies the relation(s) on which the select projects.
Related:
- GROUP BY#
- 1.
Allows partitioning relations into groups by values of a given attribute or set of attributes; when used in conjuncture with aggregations like
minormax, those aggregations will be computed independently.Related:
- HAVING#
- 1.
Allows apply conditions to the results of the aggregate values; applies to the group generated by the
group by(whereas thewhereapplies to single rows at a time)Related:
- identifier#
- 1.
Tokens that name the tables, columns, or other database objects within a command.
Broader:
Narrower:
- inner join#
- 1.
- JOIN#
- 1.
Returns a relation generated from combinations of tuples based on the provided conditions.
Compare:
- join#
- 1.
- key word#
- 1.
Tokens that have a fixed meaning in SQL (e.g. SELECT, UPDATE, or VALUES)
Broader:
- operand#
- 1.
- outer join#
- 1.
- parameter#
- 1.
- positional parameter#
- 1.
- query plan#
- 1.
- query planner#
- 1.
Component of DBMS that determines sequence of steps necessary to execute the SQL statement.
Related:
- SELECT#
- 1.
Specifies the attributes to be projected from a relation and returned.
Related:
- scalar#
- 1.
The result of a value expression.
Related:
- schema#
- 1.
Contains tables and other named objects (data types, functions, and operators). schemas are analogous to directories at the operating system level (but cannot be nested).
- self join#
- 1.
- statement#
- 1.
The SQL being executed by the DBMS.
Related:
- subquery#
- 1.
- table#
- 1.
A named collection of rows.
Related:
- table expression#
- 1.
An expression that computes a table.
Related:
- table partitioning#
- 1.
- token#
- 1.
Can be a key word, an identifier, a quoted identifier, a literal (or constant), or a special character symbol.
Narrower:
- trigger#
- 1.
- value expression#
- 1.
- view#
- 1.
- WHERE#
- 1.
Applies a condition to individual tuples from the specified relation(s)
Related:
Statements#
select#
A select statement specifies a new relation to be returned. Think of select statement as having three parts:
- from, specifying the relations
- where, specfiying how to combine and filter
- select, specifying what attributes to return
A select statement in SQL is not the same as the select operator in relational algebra.
Add distinct to treat returned relation as a set rather than a multi-set.
SQL is by default unordered. If order matters, add a clause.
table variables - use to make queries more readable, and to disambiguate relations in the where clause.
Operator <> for not equal.
Set operator union example:
select cName from Collect
union
select sName from Student;
Set operator intersect may not be available; can emulate via selecting from the same relation multiple times.
Set operator except is the difference operator.
Subqueries in select produce a value that comes out of the query. As long as it returns one value that value is used in the result tuple. This does not mean one value for all tuples, but a single value returned for the executed subquery.
where#
Subqueries in where clause allow for combining and filtering relations to then select from. Note that while some subqueries can be rewritten using a join, that may not always be the case depending on what data is desired (pay attention to duplicates!).
Subqueries can be modified with keywords in and not in to further refine results.
The keyword exists (and not exists) allows for testing of condition before selection of relation.
The keyword all allows for testing condition against all members of a relation.
The keyword any allows for testing condition against at least one member of a relation.
from#
Relations listed in the from clause are implicit cross products. It is possible to have an explicit join if needed.
A subquery in the from statement generates a new relation that we can then use in the rest of the query. These new relations often have to be marked with a new identifier.
The use of , means take the cross product. e.g.:
select * from Foo, Bar;
join#
Kinds:
- (inner) join on a condition (e.g. “theta join” from relational algebra); this is the default in SQL
- (natural) join which equates columns across tables of the same name
- inner join via using(attrs) which is like natural join but with specifying the attributes you want to be equated
- outer join, which comes in flavors:
- left
- right
- full which are similar to the theta join except for when tuples do not match the condition they are padded with NULL values.
So, diving in on a left outer join, this would include tuples from the left relation of the join that do not match the condition, padded with NULL values.
A right outer join does the same thing, but for the right relation of the join.
A full outer join takes unmatched tuples from both the left and right relations of the join (aka the union).
An outer join is not associative; order matters.
aggregations#
Performs sets of values over multiple rows of a relation. Includes:
- min
- max
- sum
- avg
- count, which allows convenient functions like count(distinct fooId)
allows addition of new clauses:
- group by
- having
SQL Advice and Performance Tips#
Many SQL statements are equivalent. The query processor should convert statements into the most efficient form, but you can provide “hints” by structuring the query with judicious use of conditions within a join statement or within a where statement.
A query processor will also typically follow the order of parentheses specified in the statement.
Prefer:
from Student join Apply using(sID)
over:
from Student natural join Apply
due to the clarity and robustness gained by explicitly specifying sID.
Think of group by as “partition by”.
If a relation can contain NULL values, be aware of how operators works:
select distinct foo from Bar;
may return NULL values, but:
select count(distinct foo) from Bar;
may not.