Relational Query Languages

A query language is a language using which a user can request data from the database.

Query Languages can be procedural (the user instructs the system to perform a set of operations on the database) or non-procedural (the user specifies only what information is needed without specifying how to obtain that information).

We will discuss the following relational query languages:

Relational Algebra (procedural)
Tuple Relational Calculus (non-procedural)
Domain Relational Calculus (non-procedural)

Relational Operators

A relational operator takes as input one or more relations and outputs a new relation.

selection of tuples
The selection operator $\sigma$ returns the rows of a relation that satisfy a selection predicate.
Ex. $\sigma _{A=B\, and\, C>5}(r)$
selection of columns (projection)
The projection operator is denoted by $\Pi$
Ex. $\Pi _{A,\, C} (r)$
Note that duplicate tuples will be eliminated automatically.
join (cartesian product)
This operation is applied on two relations and the output is another relation. The join operator ( $\times$ ) basically merges pairs of tuples, one from each relation, to create a new relation.
Ex. If r and s are two relations , $r\times s$ will be
union
The union operation ( $\cup$ ) can be performed on two relations that have the same attributes.
Ex. If r and s are two relations , $r\cup s$ will be
set difference
The set difference (-) operation can be performed on two relations that have the same attributes. The result will contain tuples that are in the first relation but not those that are in the second relation.
Ex. If r and s are two relations , r-s will be
intersection The intersection operation ( $\cap$ ) can be performed on two relations that have the same attributes. The result will contain tuples that are both in the first and second relations. Ex. If r and s are two relations , $r\cap s$ will be Note that $r\cap s = r-(r-s)$
natural join The natural join ( $\bowtie$ ) of r and s results in the set of all combinations of tuples in r and s that are equal on their common attribute names. If r and s are two relations , $r\bowtie s$ will be The result of the natural join operation is usually smaller than that of the cartesian product operation. Note that natural join is associative i.e. $(a\bowtie b) \bowtie c = a\bowtie (b \bowtie c)$ and commutative i.e. $a \bowtie b = b \bowtie a$
theta join $r\bowtie _\theta s = \sigma _\theta (r \times s)$
outer join The outer join operation ( $\sqsupset\bowtie$ , $\bowtie\sqsubset$ , $\sqsupset\bowtie\sqsubset$ ) ia an extension of the join (natural join) operation that avoids loss of information. It computes the join and then adds tuples from one relation that do not match tuples in the other relation to the result of the join. null is used to denote missing values in the result.
Ex. if we have two relations instructor and teaches, given by and respectively, then the natural join ( $instructor\bowtie teaches$ ) would be:
The left outer join ( $instructor \sqsupset\bowtie teaches$ ) would be: The right outer join ( $instructor \bowtie\sqsubset teaches$ ) would be: and the full outer join ( $instructor \sqsupset\bowtie\sqsubset teaches$ ) would be:
rename The rename operator ( $\rho$ ) allows us to name, and therefore refer to, the results of a relational operation.
assignment The assignment operator ( $\leftarrow$ ) lets us assign the result of an operation to a temporary relation variable.

More about null values

Two nulls are treated to be the same
The result of any arithmetic operation involving a null value is null
Comparisons with null values return the special truth value: unknown
Three-valued logic using the truth value unknown:
- OR:
  (unknown or true) = true,
  (unknown or false) = unknown
  (unknown or unknown) = unknown
- AND:
  (true and unknown) = unknown,
  (false and unknown) = false,
  (unknown and unknown) = unknown
- NOT:
  (not unknown) = unknown
The result of select predicate is treated as false if it evaluates to unknown

PreviousKeys NextRelational Algebra

Last updated 3 years ago

Relational Operators

A relational operator takes as input one or more relations and outputs a new relation.

selection of tuples

The selection operator $\sigma$ returns the rows of a relation that satisfy a selection predicate.

Ex. $\sigma _{A=B\, and\, C>5}(r)$

selection of columns (projection)

The projection operator is denoted by $\Pi$

Ex. $\Pi _{A,\, C} (r)$

Note that duplicate tuples will be eliminated automatically.

join (cartesian product)

This operation is applied on two relations and the output is another relation. The join operator ( $\times$ ) basically merges pairs of tuples, one from each relation, to create a new relation.

Ex. If r and s are two relations , $r\times s$ will be

union

The union operation ( $\cup$ ) can be performed on two relations that have the same attributes.

Ex. If r and s are two relations , $r\cup s$ will be

set difference

The set difference (-) operation can be performed on two relations that have the same attributes. The result will contain tuples that are in the first relation but not those that are in the second relation.

Ex. If r and s are two relations , r-s will be

intersection The intersection operation ( $\cap$ ) can be performed on two relations that have the same attributes. The result will contain tuples that are both in the first and second relations. Ex. If r and s are two relations , $r\cap s$ will be Note that $r\cap s = r-(r-s)$

natural join The natural join ( $\bowtie$ ) of r and s results in the set of all combinations of tuples in r and s that are equal on their common attribute names. If r and s are two relations , $r\bowtie s$ will be The result of the natural join operation is usually smaller than that of the cartesian product operation. Note that natural join is associative i.e. $(a\bowtie b) \bowtie c = a\bowtie (b \bowtie c)$ and commutative i.e. $a \bowtie b = b \bowtie a$

theta join $r\bowtie _\theta s = \sigma _\theta (r \times s)$

outer join The outer join operation ( $\sqsupset\bowtie$ , $\bowtie\sqsubset$ , $\sqsupset\bowtie\sqsubset$ ) ia an extension of the join (natural join) operation that avoids loss of information. It computes the join and then adds tuples from one relation that do not match tuples in the other relation to the result of the join. null is used to denote missing values in the result.

Ex. if we have two relations instructor and teaches, given by and respectively, then the natural join ( $instructor\bowtie teaches$ ) would be:

The left outer join ( $instructor \sqsupset\bowtie teaches$ ) would be: The right outer join ( $instructor \bowtie\sqsubset teaches$ ) would be: and the full outer join ( $instructor \sqsupset\bowtie\sqsubset teaches$ ) would be:

rename The rename operator ( $\rho$ ) allows us to name, and therefore refer to, the results of a relational operation.

assignment The assignment operator ( $\leftarrow$ ) lets us assign the result of an operation to a temporary relation variable.

More about null values

Two nulls are treated to be the same

The result of any arithmetic operation involving a null value is null

Comparisons with null values return the special truth value: unknown

Three-valued logic using the truth value unknown:

OR:
(unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknown
AND:
(true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
NOT:
(not unknown) = unknown

The result of select predicate is treated as false if it evaluates to unknown