database concepts

Advanced SQL : advanced querying

Subqueries

Subqueries, or nested queries, are a second SELECT query contained within a first SELECT query. The subquery can be used within a WHERE, HAVING or FROM clause of another SELECT query. Very powerful queries can be designed using this technique.

There are two types of subqueries:

Single-row: returns only one row of data
Multiple-row: returns more than one row of data

Single row subquery
The general syntax of a single row subquery is:

     SELECT ColumnList

     FROM TableName

     WHERE Condition

          (SELECT ColumnList

               FROM TableName

               WHERE Condition)

There are certain rules we need to follow when we create a subquery:

The subquery must be enclosed by brackets
The ORDER BY clause cannot be used in a subquery
The subquery is used on the right side of the condition

When a statement is written with a subquery, the inner query is executed first. The inner query returns a value or a set of values to the outer query. The outer query is then executed with the result from the inner query.

Multiple row subquery
A multiple-row subquery returns more than one row of data. The operators used in a single-row subqueries relational operators (=, <>, >, >=, <, <=) cannot be used in multiple-row subqueries. Instead, other operators must be used.

Where a subquery can return zero, one or many values, there are a number of operators that can be used. These include:

Operator	Usage	Meaning
`IN`	value `IN` table	true if the value is in the table
`EXISTS`	`EXISTS`(table)	true if the table is not empty

And the following which are used in conjunction with one of the traditional operators: =, <>, >, >=, <, <=

`ANY`		compares the given value to each value returned by the subquery
	`value = ANY`( table )	True if the value equals a value in the table
`ALL`		compares the given value to every value returned by the subquery
	`value <> ALL`( table )	True if the value does not equal any value in the table.

Some further examples are:

`value < ANY`	value is less than at least one value returned by the subquery, that is value is less than the maximum value returned by the subquery
`value = ANY`	equivalent to `IN`
`value > ANY`	value is more that at least one value returned by the subquery, that is value is greater than the minimum value returned by the subquery
`value >= ALL`	value is bigger than or equal to all values returned by the subquery, that is value is equal to or bigger than the maximum value returned by the subquery
`value < ALL`	value is less than all values returned by the subquery, that is value is less than the minimum value returned by the subquery