Subqueries

Remarks#

Subqueries can appear in different clauses of an outer query, or in the set operation.

They must be enclosed in parentheses (). If the result of the subquery is compared to something else, the number of columns must match. Table aliases are required for subqueries in the FROM clause to name the temporary table.

Subquery in WHERE clause

Subquery in FROM clause

Subquery in SELECT clause

Subqueries in FROM clause

You can use subqueries to define a temporary table and use it in the FROM clause of an “outer” query.

SELECT * FROM (SELECT city, temp_hi - temp_lo AS temp_var FROM weather) AS w
WHERE temp_var > 20;

The above finds cities from the weather table whose daily temperature variation is greater than 20. The result is:

.

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:

Subqueries in SELECT clause

Subqueries can also be used in the SELECT part of the outer query. The following query shows all weather table columns with the corresponding states from the cities table.

SELECT w.*,  (SELECT c.state FROM cities AS c WHERE c.name = w.city ) AS state 
FROM weather AS w;

Filter query results using query on different table

Correlated Subqueries

Correlated (also known as Synchronized or Coordinated) Subqueries are nested queries that make references to the current row of their outer query:

SELECT EmployeeId
    FROM Employee AS eOuter
    WHERE Salary > (
       SELECT AVG(Salary)
       FROM Employee eInner
       WHERE eInner.DepartmentId = eOuter.DepartmentId
    )

Subquery SELECT AVG(Salary) ... is correlated because it refers to Employee row eOuter from its outer query.