Subqueries can be used in the WHERE and HAVING clauses of a SELECT. These are useful where you want to select records or groups from a table with a condition that depends on data in the table itself.
Suppose you want to find all the employees who have the same grade as John Brown, without knowing his grade:
SELECT name, grade FROM employee
WHERE grade = (SELECT grade FROM employee WHERE name='John Brown')
This subquery returns one value, the grade of John Brown.
You can use subqueries that return several values, but you must insert the ANY or ALL keywords before the subquery. For example, to list employees with higher grades than any other employee:
SELECT name, grade FROM employee
WHERE grade < ANY (SELECT grade FROM employee)
In effect, this selects employees that have grades higher than employees with the lowest grade.
For more examples, refer to An Introduction to Database Systems, chapter 6, by C. J. Date.