SQL Subqueries in WHERE clause


The following example finds cities (from the cities example) whose population is below the average temperature (obtained via a sub-qquery):

SELECT name, pop2000 FROM cities 
WHERE pop2000 < (SELECT avg(pop2000)  FROM cities);

Here: the subquery (SELECT avg(pop2000) FROM cities) is used to specify conditions in the WHERE clause. The result is:

San Francisco776733
ST LOUIS348189
Kansas City146866