Temporal relations

Temporal Relations

Temporal relation:

Temporal relation = a relation that is computed (retrieved) by a SELECT clause that exists temporally in the database system

Temporal relations are created/produced using one the following methods:

Using a bracketed SELECT command inside the FROM clause:
Note:

Using the WITH clause (introduced in SQL99 as syntactical sugar)

WITH ALIAS as (SELECT .....) /* With clause */ SELECT ... FROM ... ...

Note:

The syntax requires the use of an alias as the name for the temporal relation
The WITH clause is not implemented in MySQ (version 5).

Temporal relations defined inside the FROM clause

Recall:
Fact:

Therefore:

The result of a SELECT command (a relation) can be used as a temporal relation in the FROM clause !!!
Requirement:

Example:

Find fname, lname of male employees with salary > 50000

Solution:

We can first find all the male employees: select fname, lname, salary from employee where sex='M'
And then use this result to find those (male) employees earning > 40000: select * from (select fname, lname, salary from employee where sex='M') R1 where R1.salary > 40000

Notice that we must use an alias R1 to name the result relation produced by the SELECT command

Important notes on Temporal relations

You can use multiple temporal relations in the FROM clause:

SELECT .... FROM .... , (SELECT .... ) R1 , (SELECT .... ) R2 , ... WHERE .... ...

You cannot use a temporal relation to create another temporal relation:

Not allowed: SELECT .... FROM .... , (SELECT .... ) R1 , (SELECT .... FROM R1 ... ) R2 , ... WHERE .... ...

You cannot use R1 to create R2 !!!

Creating a temporal relation using the WITH clause

Syntax of the WITH clause:

WITH ALIAS as ( SELECT query ) [, ALIAS2 as (SELECT query2) ....] SELECT query

Effect:

The SELECT queries inside the WITH clause are executed:

The SELECT queries in the WITH clause are executed in the order they occurs in the WITH clause
Each SELECT query will return (create) a temporal relation which will be named by the given ALIAS)

The SELECT query following the WITH clause is executed using all the temporal relations created in the WITH clause

Example:

Solution using WITH clause

This query finds John Smith: (SELECT * FROM employee WHERE fname='John' AND lname='Smith')
Use the result to find the dependents of John Smith: with R1 as (SELECT * FROM employee WHERE fname='John' AND lname='Smith') select * from dependent where essn IN (select ssn from R1)

Important property of the WITH clause

Facts:

The sub-queries inside a WITH clause are executed in the order specified inside the WITH clause
A later sub-query can use the result of a prior sub-query !!!

Refining a query (= adding more conditions) using a WITH clause

The WITH clause can help you refine the result of a query.

Example:

Find fname, lname of male employee in the 'Research' department who earn more than $30000

Solution:

First, we find all male employees: R1 = (select * from employee where sex='M')
Then find the male employees who works in the Research department: R2 = (select * from R1, department where dno=dnumber and dname='Research')
Finally, get those in R2 who earn more than $30000: select fname, lname from R2 where salary > 30000 /* ... who earn > 30000 */
Using the WITH clause to write the query: with R1 as (select * from employee where sex='M') , /* Male employees */ R2 as (select * from R1, department where dno=dnumber and dname='Research') /* ... that are Research emps */ select fname, lname from R2 where salary > 30000 /* ... who earn > 30000 */

Note: