Subquery: prelude to nested query

Many of the tuple condition clauses uses:

( set of values )

Examples:

attr IN ( set of values ) attr >= ANY ( set of values ) attr >= ALL ( set of values ) EXISTS ( set of values ) ....

We will now learn a method to form (= compute) a set of values:
subquery

Subquery: prelude to nested query

Syntax of a subquery:
( SELECT-command )
Meaning of a subquery:
The result of a subquery is:

The relation (= set) of tuples selected by the SQL SELECT command in the subquery

Subquery: example

Suppose the department relation contains:

dname dnumber mgrssn mgrstartdate --------------- ----------- --------- ------------ Research 5 333445555 22-MAY-78 <------- **** Administration 4 987654321 01-JAN-85 Headquarters 1 888665555 19-JUN-71

Then the subquery:

( SELECT dnumber FROM department WHERE dname='Research' )

will return the following output set:

( 5 ) // Note: this is a set of 1 element !

Subquery: how to use

Where in the SELECT command can you put a sub-query:

A sub-query can be use inside the relation-list in the FROM clause:

SELECT attribute-list FROM .... (subquery) .... WHERE ....

A sub-query can be use inside the WHERE clause:

SELECT attribute-list FROM .... WHERE .... (subquery) ....

Subquery: usage within the WHERE clause

The sub-query inside a WHERE clause will compute the SET of value in these conditions:

WHERE atomic-value ReationalOperator atomic-value
WHERE atomic-value IN (SET of values)
WHERE atomic-value NOT IN (SET of values)
WHERE atomic-value ReationalOperator ANY (SET of values)
WHERE atomic-value ReationalOperator ALL (SET of values)
WHERE EXISTS (SET of values)
WHERE atomic-value IS NULL
WHERE atomic-value LIKE 'wildcard-expression'

You can use a subquery in any place where you see: (SET of values)

Example:

ssn IN (select essn from dependent)

You can also use a subquery that return 1 value in any place you see: atomic-value