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)