Subqueries

[sukishan]

Subqueries
One SELECT statement can be used inside another, allowing the result of executing one query to be used in the WHERE rules of the other SELECT statement. Where one SELECT statement appears within another SELECT statement's WHERE clause it is known as a SUBQUERY.

One limitation of subqueries is that it can only return one attribute. This means that the subquery can only have one attribute in its SELECT line. If you supply more than one attribute the system will report an error.

Subqueries are generally used in situations where one might normally use a self join or a view. Subqueries tend to be much easier to understand.

Simple Example
Who in the database is older than Jim Smith?

SELECT dob FROM driver WHERE name = 'Jim Smith'
output:
dob
11 Jan 1980

SELECT name FROM driver WHERE dob > '11 Jan 1980';

output:
name
Bob Smith
Bob Jones

SELECT name
FROM driver
WHERE dob > (SELECT dob FROM driver WHERE name = 'Jim Smith')
This subquery works well, and is simple to understand, but you must take care that the subquery returns only 1 row. If there were two people called Jim Smith, the query would return two different dates of birth, and this would break the query. To get around this problem, we use ANY or ALL.