Subqueries

Subqueries (also known as inner queries or nested queries) are queries within a query. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses.

• Subquery operators
  • [ NOT ] EXISTS
  • [ NOT ] IN
• SELECT clause subqueries
• FROM clause subqueries
• WHERE clause subqueries
• HAVING clause subqueries
• Subquery categories
  • Correlated subqueries
  • Non-correlated subqueries
  • Scalar subqueries
  • Non-scalar subqueries

Subquery operators

• [ NOT ] EXISTS
• [ NOT ] IN

[ NOT ] EXISTS

The EXISTS operator returns all rows where a correlated subquery produces one or more matches for that row. NOT EXISTS returns all rows where a correlated subquery produces zero matches for that row. Only correlated subqueries are supported.

Syntax

[NOT] EXISTS (subquery)

[ NOT ] IN

The IN operator returns all rows where a given expression’s value can be found in the results of a correlated subquery. NOT IN returns all rows where a given expression’s value cannot be found in the results of a subquery or list of values.

Syntax

expression [NOT] IN (subquery|list-literal)

Examples

SELECT  time,  room,  temp FROM  home WHERE  room IN (  SELECT  DISTINCT room  FROM  home_actions  )

SELECT  time,  room,  temp FROM  home WHERE  room NOT IN (  SELECT  DISTINCT room  FROM  home_actions  )

SELECT  time,  room,  temp FROM home WHERE room IN ('Bathroom', 'Bedroom', 'Kitchen')

SELECT  time,  room,  temp FROM home WHERE room NOT IN ('Bathroom', 'Bedroom', 'Kitchen')

SELECT clause subqueries

SELECT clause subqueries use values returned from the inner query as part of the outer query’s SELECT list. The SELECT clause only supports scalar subqueries that return a single value per execution of the inner query. The returned value can be unique per row.

Syntax

SELECT [expression1[, expression2, ..., expressionN],] (<subquery>)

Examples

SELECT  time,  room,  co,  (  SELECT  MAX(description)  FROM  home_actions  WHERE  time = home.time  AND room = home.room  AND level != 'ok'  ) AS "Alert Description" FROM  home ORDER BY  room,  time

FROM clause subqueries

FROM clause subqueries return a set of results that is then queried and operated on by the outer query.

Syntax

SELECT expression1[, expression2, ..., expressionN] FROM (<subquery>)

Examples

SELECT  AVG(max_co) AS avg_max_co,  AVG(max_hum) AS avg_max_hum,  AVG(max_temp) AS avg_max_temp FROM  (  SELECT  room,  MAX(co) AS max_co,  MAX(hum) AS max_hum,  MAX(temp) AS max_temp  FROM  home  GROUP BY  room  )

WHERE clause subqueries

WHERE clause subqueries compare an expression to the result of the subquery and return true or false. Rows that evaluate to false or NULL are filtered from results. The WHERE clause supports correlated and non-correlated subqueries as well as scalar and non-scalar subqueries (depending on the the operator used in the predicate expression).

Syntax

SELECT  expression1[, expression2, ..., expressionN] FROM  <measurement> WHERE  expression operator (<subquery>)

Examples

SELECT  * FROM  home WHERE  temp > (  SELECT  AVG(temp)  FROM  home  )

SELECT  * FROM  home WHERE  time IN (  SELECT  DISTINCT time  FROM  home_actions  WHERE  level = 'warn'  )

SELECT  time,  room,  temp FROM  home outer_query WHERE  temp > (  SELECT  median(temp) AS temp  FROM  home  WHERE  room = outer_query.room  GROUP BY  room  ) ORDER BY room, time

HAVING clause subqueries

HAVING clause subqueries compare an expression that uses aggregate values returned by aggregate functions in the SELECT clause to the result of the subquery and return true or false. Rows that evaluate to false or NULL are filtered from results. The HAVING clause supports correlated and non-correlated subqueries as well as scalar and non-scalar subqueries (depending on the the operator used in the predicate expression).

Syntax

SELECT  aggregate_expression1[, aggregate_expression2, ..., aggregate_expressionN] FROM  <measurement> WHERE  <conditional_expression> GROUP BY  column_expression1[, column_expression2, ..., column_expressionN] HAVING  expression operator (<subquery>)

Examples

SELECT  DATE_BIN(INTERVAL '2 hours', time) AS "2-hour block",  AVG(temp) AS avg_temp FROM  home GROUP BY  1 HAVING  avg_temp > (  SELECT  MEDIAN(temp)  FROM  home  )

SELECT  date_bin(INTERVAL '2 hours', time) AS "2-hour block",  max(co) AS max_co,  max(temp) as max_temp FROM  home GROUP BY  1,  room HAVING  "2-hour block" IN (  SELECT  DISTINCT time  FROM  home_actions  WHERE  level = 'warn'  )

SELECT  time,  room,  temp FROM  home outer_query WHERE  temp > (  SELECT  median(temp) AS temp  FROM  home  WHERE  room = outer_query.room  GROUP BY  room  ) ORDER BY room, time

Subquery categories

SQL subqueries can be categorized as one or more of the following based on the behavior of the subquery:

• correlated or non-correlated
• scalar or non-scalar

Correlated subqueries

In a correlated subquery, the inner query depends on the values of the current row being processed.

In the query below, the inner query (SELECT temp_avg FROM weather WHERE location = home.room) depends on data (home.room) from the outer query (SELECT time, room, temp FROM home) and is therefore a correlated subquery.

SELECT  time,  room,  temp FROM  home WHERE  temp = (  SELECT  temp_avg  FROM  weather  WHERE  location = home.room  )

Non-correlated subqueries

In a non-correlated subquery, the inner query doesn’t depend on the outer query and executes independently. The inner query executes first, and then passes the results to the outer query.

In the query below, the inner query (SELECT MIN(temp_avg) FROM weather) can run independently from the outer query (SELECT time, temp FROM home) and is therefore a non-correlated subquery.

SELECT  time,  temp FROM  home WHERE  temp < (  SELECT  MIN(temp_avg)  FROM  weather  )

Scalar subqueries

A scalar subquery returns a single value (one column of one row). If no rows are returned, the subquery returns NULL.

The example subquery below returns the average value of a specified column. This value is a single scalar value.

SELECT * FROM home WHERE co > (SELECT avg(co) FROM home)

Non-scalar subqueries

A non-scalar subquery returns 0, 1, or multiple rows, each of which may contain 1 or multiple columns. For each column, if there is no value to return, the subquery returns NULL. If no rows qualify to be returned, the subquery returns 0 rows.

The example subquery below returns all distinct values in a column. Multiple values are returned.

SELECT * FROM home WHERE room IN (SELECT DISTINCT room FROM home_actions)