This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch asf-site
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/asf-site by this push:
new 2061141996 Publish built docs triggered by
0681004613c3ca893deab4c331d6783166899206
2061141996 is described below
commit 20611419963435c20aba51ecc1cb7fc034886e37
Author: github-actions[bot] <github-actions[bot]@users.noreply.github.com>
AuthorDate: Tue May 7 17:17:23 2024 +0000
Publish built docs triggered by 0681004613c3ca893deab4c331d6783166899206
---
_sources/user-guide/sql/subqueries.md.txt | 371 +++++++++++++++++++--
searchindex.js | 2 +-
user-guide/sql/index.html | 11 +-
user-guide/sql/subqueries.html | 520 +++++++++++++++++++++++++++---
4 files changed, 823 insertions(+), 81 deletions(-)
diff --git a/_sources/user-guide/sql/subqueries.md.txt
b/_sources/user-guide/sql/subqueries.md.txt
index 6055b0fc5b..ee75a6a157 100644
--- a/_sources/user-guide/sql/subqueries.md.txt
+++ b/_sources/user-guide/sql/subqueries.md.txt
@@ -19,80 +19,391 @@
# Subqueries
-DataFusion supports `EXISTS`, `NOT EXISTS`, `IN`, `NOT IN` and Scalar
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.
-The examples below are based on the following table.
+The examples below are based on the following tables.
```sql
-select * from x;
+SELECT * FROM x;
+
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1 | 2 |
+----------+----------+
+| 2 | 4 |
++----------+----------+
+```
+
+```sql
+SELECT * FROM y;
+
++--------+--------+
+| number | string |
++--------+--------+
+| 1 | one |
++--------+--------+
+| 2 | two |
++--------+--------+
+| 3 | three |
++--------+--------+
+| 4 | four |
++--------+--------+
```
-## EXISTS
+## Subquery operators
-The `EXISTS` syntax can be used to find all rows in a relation where a
correlated subquery produces one or more matches
-for that row. Only correlated subqueries are supported.
+- [[ NOT ] EXISTS](#-not--exists)
+- [[ NOT ] IN](#-not--in)
+
+### [ NOT ] EXISTS
+
+The `EXISTS` operator returns all rows where a
+_[correlated subquery](#correlated-subqueries)_ 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.
```sql
-select * from x y where exists (select * from x where x.column_1 = y.column_1);
+[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](#correlated-subqueries)_.
+`NOT IN` returns all rows where a given expression’s value cannot be found in
+the results of a subquery or list of values.
+
+```sql
+expression [NOT] IN (subquery|list-literal)
+```
+
+#### Examples
+
+```sql
+SELECT * FROM x WHERE column_1 IN (1,3);
+
+----------+----------+
| column_1 | column_2 |
+----------+----------+
| 1 | 2 |
+----------+----------+
-1 row in set.
```
-## NOT EXISTS
+```sql
+SELECT * FROM x WHERE column_1 NOT IN (1,3);
+
++----------+----------+
+| column_1 | column_2 |
++----------+----------+
+| 2 | 4 |
++----------+----------+
+```
+
+## 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](#scalar-subqueries) that
+return a single value per execution of the inner query.
+The returned value can be unique per row.
+
+```sql
+SELECT [expression1[, expression2, ..., expressionN],] (<subquery>)
+```
+
+**Note**: `SELECT` clause subqueries can be used as an alternative to `JOIN`
+operations.
-The `NOT EXISTS` syntax can be used to find all rows in a relation where a
correlated subquery produces zero matches
-for that row. Only correlated subqueries are supported.
+### Example
```sql
-select * from x y where not exists (select * from x where x.column_1 =
y.column_1);
-0 rows in set.
+SELECT
+ column_1,
+ (
+ SELECT
+ first_value(string)
+ FROM
+ y
+ WHERE
+ number = x.column_1
+ ) AS "numeric string"
+FROM
+ x;
+
++----------+----------------+
+| column_1 | numeric string |
++----------+----------------+
+| 1 | one |
+| 2 | two |
++----------+----------------+
```
-## IN
+## FROM clause subqueries
-The `IN` syntax can be used to find all rows in a relation where a given
expression's value can be found in the
-results of a correlated subquery.
+`FROM` clause subqueries return a set of results that is then queried and
+operated on by the outer query.
```sql
-select * from x where column_1 in (select column_1 from x);
+SELECT expression1[, expression2, ..., expressionN] FROM (<subquery>)
+```
+
+### Example
+
+The following query returns the average of maximum values per room.
+The inner query returns the maximum value for each field from each room.
+The outer query uses the results of the inner query and returns the average
+maximum value for each field.
+
+```sql
+SELECT
+ column_2
+FROM
+ (
+ SELECT
+ *
+ FROM
+ x
+ WHERE
+ column_1 > 1
+ );
+
++----------+
+| column_2 |
++----------+
+| 4 |
++----------+
+```
+
+## 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).
+
+```sql
+SELECT
+ expression1[, expression2, ..., expressionN]
+FROM
+ <measurement>
+WHERE
+ expression operator (<subquery>)
+```
+
+**Note:** `WHERE` clause subqueries can be used as an alternative to `JOIN`
+operations.
+
+### Examples
+
+#### `WHERE` clause with scalar subquery
+
+The following query returns all rows with `column_2` values above the average
+of all `number` values in `y`.
+
+```sql
+SELECT
+ *
+FROM
+ x
+WHERE
+ column_2 > (
+ SELECT
+ AVG(number)
+ FROM
+ y
+ );
+
+----------+----------+
| column_1 | column_2 |
+----------+----------+
-| 1 | 2 |
+| 2 | 4 |
+----------+----------+
-1 row in set.
```
-## NOT IN
+#### `WHERE` clause with non-scalar subquery
-The `NOT IN` syntax can be used to find all rows in a relation where a given
expression's value can not be found in the
-results of a correlated subquery.
+Non-scalar subqueries must use the `[NOT] IN` or `[NOT] EXISTS` operators and
+can only return a single column.
+The values in the returned column are evaluated as a list.
+
+The following query returns all rows with `column_2` values in table `x` that
+are in the list of numbers with string lengths greater than three from table
+`y`.
```sql
-select * from x where column_1 not in (select column_1 from x);
-0 rows in set.
+SELECT
+ *
+FROM
+ x
+WHERE
+ column_2 IN (
+ SELECT
+ number
+ FROM
+ y
+ WHERE
+ length(string) > 3
+ );
+
++----------+----------+
+| column_1 | column_2 |
++----------+----------+
+| 2 | 4 |
++----------+----------+
```
-## Scalar Subquery
+### `WHERE` clause with correlated subquery
-A scalar subquery can be used to produce a single value that can be used in
many different contexts in a query. Here
-is an example of a filter using a scalar subquery. Only correlated subqueries
are supported.
+The following query returns rows with `column_2` values from table `x` greater
+than the average `string` value length from table `y`.
+The subquery in the `WHERE` clause uses the `column_1` value from the outer
+query to return the average `string` value length for that specific value.
```sql
-select * from x y where column_1 < (select sum(column_2) from x where
x.column_1 = y.column_1);
+SELECT
+ *
+FROM
+ x
+WHERE
+ column_2 > (
+ SELECT
+ AVG(length(string))
+ FROM
+ y
+ WHERE
+ number = x.column_1
+ );
+
+----------+----------+
| column_1 | column_2 |
+----------+----------+
-| 1 | 2 |
+| 2 | 4 |
+----------+----------+
-1 row in set.
```
+
+## 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_ 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).
+
+```sql
+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
+
+The following query calculates the averages of even and odd numbers in table
`y`
+and returns the averages that are equal to the maximum value of `column_1`
+in table `x`.
+
+#### `HAVING` clause with a scalar subquery
+
+```sql
+SELECT
+ AVG(number) AS avg,
+ (number % 2 = 0) AS even
+FROM
+ y
+GROUP BY
+ even
+HAVING
+ avg = (
+ SELECT
+ MAX(column_1)
+ FROM
+ x
+ );
+
++-------+--------+
+| avg | even |
++-------+--------+
+| 2 | false |
++-------+--------+
+```
+
+#### `HAVING` clause with a non-scalar subquery
+
+Non-scalar subqueries must use the `[NOT] IN` or `[NOT] EXISTS` operators and
+can only return a single column.
+The values in the returned column are evaluated as a list.
+
+The following query calculates the averages of even and odd numbers in table
`y`
+and returns the averages that are in `column_1` of table `x`.
+
+```sql
+SELECT
+ AVG(number) AS avg,
+ (number % 2 = 0) AS even
+FROM
+ y
+GROUP BY
+ even
+HAVING
+ avg IN (
+ SELECT
+ column_1
+ FROM
+ x
+ );
+
++-------+--------+
+| avg | even |
++-------+--------+
+| 2 | false |
++-------+--------+
+```
+
+## Subquery categories
+
+Subqueries can be categorized as one or more of the following based on the
+behavior of the subquery:
+
+- [correlated](#correlated-subqueries) or
+ [non-correlated](#non-correlated-subqueries)
+- [scalar](#scalar-subqueries) or [non-scalar](#non-scalar-subqueries)
+
+### Correlated subqueries
+
+In a **correlated** subquery, the inner query depends on the values of the
+current row being processed.
+
+**Note:** DataFusion internally rewrites correlated subqueries into JOINs to
+improve performance. In general correlated subqueries are **less performant**
+than non-correlated subqueries.
+
+### 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.
+
+### Scalar subqueries
+
+A **scalar** subquery returns a single value (one column of one row).
+If no rows are returned, the subquery returns NULL.
+
+### 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.
diff --git a/searchindex.js b/searchindex.js
index ba279651cc..a932e4a080 100644
--- a/searchindex.js
+++ b/searchindex.js
@@ -1 +1 @@
-Search.setIndex({"alltitles": {"!=": [[39, "op-neq"]], "!~": [[39,
"op-re-not-match"]], "!~*": [[39, "op-re-not-match-i"]], "!~~": [[39, "id18"]],
"!~~*": [[39, "id19"]], "#": [[39, "op-bit-xor"]], "%": [[39, "op-modulo"]],
"&": [[39, "op-bit-and"]], "(relation, name) tuples in logical fields and
logical columns are unique": [[7,
"relation-name-tuples-in-logical-fields-and-logical-columns-are-unique"]], "*":
[[39, "op-multiply"]], "+": [[39, "op-plus"]], "-": [[39, "op-minus"]], "/":
[[3 [...]
\ No newline at end of file
+Search.setIndex({"alltitles": {"!=": [[39, "op-neq"]], "!~": [[39,
"op-re-not-match"]], "!~*": [[39, "op-re-not-match-i"]], "!~~": [[39, "id18"]],
"!~~*": [[39, "id19"]], "#": [[39, "op-bit-xor"]], "%": [[39, "op-modulo"]],
"&": [[39, "op-bit-and"]], "(relation, name) tuples in logical fields and
logical columns are unique": [[7,
"relation-name-tuples-in-logical-fields-and-logical-columns-are-unique"]], "*":
[[39, "op-multiply"]], "+": [[39, "op-plus"]], "-": [[39, "op-minus"]], "/":
[[3 [...]
\ No newline at end of file
diff --git a/user-guide/sql/index.html b/user-guide/sql/index.html
index 8521fe1460..9c5af914e4 100644
--- a/user-guide/sql/index.html
+++ b/user-guide/sql/index.html
@@ -500,11 +500,12 @@
</ul>
</li>
<li class="toctree-l1"><a class="reference internal"
href="subqueries.html">Subqueries</a><ul>
-<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#exists">EXISTS</a></li>
-<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#not-exists">NOT EXISTS</a></li>
-<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#in">IN</a></li>
-<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#not-in">NOT IN</a></li>
-<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#scalar-subquery">Scalar Subquery</a></li>
+<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#subquery-operators">Subquery operators</a></li>
+<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#select-clause-subqueries">SELECT clause
subqueries</a></li>
+<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#from-clause-subqueries">FROM clause subqueries</a></li>
+<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#where-clause-subqueries">WHERE clause subqueries</a></li>
+<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#having-clause-subqueries">HAVING clause
subqueries</a></li>
+<li class="toctree-l2"><a class="reference internal"
href="subqueries.html#subquery-categories">Subquery categories</a></li>
</ul>
</li>
<li class="toctree-l1"><a class="reference internal"
href="ddl.html">DDL</a><ul>
diff --git a/user-guide/sql/subqueries.html b/user-guide/sql/subqueries.html
index aac89bd524..e865297c68 100644
--- a/user-guide/sql/subqueries.html
+++ b/user-guide/sql/subqueries.html
@@ -449,29 +449,157 @@
<nav id="bd-toc-nav">
<ul class="visible nav section-nav flex-column">
<li class="toc-h2 nav-item toc-entry">
- <a class="reference internal nav-link" href="#exists">
- EXISTS
+ <a class="reference internal nav-link" href="#subquery-operators">
+ Subquery operators
</a>
+ <ul class="nav section-nav flex-column">
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#not-exists">
+ [ NOT ] EXISTS
+ </a>
+ </li>
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#not-in">
+ [ NOT ] IN
+ </a>
+ <ul class="nav section-nav flex-column">
+ <li class="toc-h4 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#examples">
+ Examples
+ </a>
+ </li>
+ </ul>
+ </li>
+ </ul>
</li>
<li class="toc-h2 nav-item toc-entry">
- <a class="reference internal nav-link" href="#not-exists">
- NOT EXISTS
+ <a class="reference internal nav-link" href="#select-clause-subqueries">
+ SELECT clause subqueries
</a>
+ <ul class="nav section-nav flex-column">
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#example">
+ Example
+ </a>
+ </li>
+ </ul>
</li>
<li class="toc-h2 nav-item toc-entry">
- <a class="reference internal nav-link" href="#in">
- IN
+ <a class="reference internal nav-link" href="#from-clause-subqueries">
+ FROM clause subqueries
</a>
+ <ul class="nav section-nav flex-column">
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#id1">
+ Example
+ </a>
+ </li>
+ </ul>
</li>
<li class="toc-h2 nav-item toc-entry">
- <a class="reference internal nav-link" href="#not-in">
- NOT IN
+ <a class="reference internal nav-link" href="#where-clause-subqueries">
+ WHERE clause subqueries
</a>
+ <ul class="nav section-nav flex-column">
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#id2">
+ Examples
+ </a>
+ <ul class="nav section-nav flex-column">
+ <li class="toc-h4 nav-item toc-entry">
+ <a class="reference internal nav-link"
href="#where-clause-with-scalar-subquery">
+ <code class="docutils literal notranslate">
+ <span class="pre">
+ WHERE
+ </span>
+ </code>
+ clause with scalar subquery
+ </a>
+ </li>
+ <li class="toc-h4 nav-item toc-entry">
+ <a class="reference internal nav-link"
href="#where-clause-with-non-scalar-subquery">
+ <code class="docutils literal notranslate">
+ <span class="pre">
+ WHERE
+ </span>
+ </code>
+ clause with non-scalar subquery
+ </a>
+ </li>
+ </ul>
+ </li>
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link"
href="#where-clause-with-correlated-subquery">
+ <code class="docutils literal notranslate">
+ <span class="pre">
+ WHERE
+ </span>
+ </code>
+ clause with correlated subquery
+ </a>
+ </li>
+ </ul>
</li>
<li class="toc-h2 nav-item toc-entry">
- <a class="reference internal nav-link" href="#scalar-subquery">
- Scalar Subquery
+ <a class="reference internal nav-link" href="#having-clause-subqueries">
+ HAVING clause subqueries
</a>
+ <ul class="nav section-nav flex-column">
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#id3">
+ Examples
+ </a>
+ <ul class="nav section-nav flex-column">
+ <li class="toc-h4 nav-item toc-entry">
+ <a class="reference internal nav-link"
href="#having-clause-with-a-scalar-subquery">
+ <code class="docutils literal notranslate">
+ <span class="pre">
+ HAVING
+ </span>
+ </code>
+ clause with a scalar subquery
+ </a>
+ </li>
+ <li class="toc-h4 nav-item toc-entry">
+ <a class="reference internal nav-link"
href="#having-clause-with-a-non-scalar-subquery">
+ <code class="docutils literal notranslate">
+ <span class="pre">
+ HAVING
+ </span>
+ </code>
+ clause with a non-scalar subquery
+ </a>
+ </li>
+ </ul>
+ </li>
+ </ul>
+ </li>
+ <li class="toc-h2 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#subquery-categories">
+ Subquery categories
+ </a>
+ <ul class="nav section-nav flex-column">
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#correlated-subqueries">
+ Correlated subqueries
+ </a>
+ </li>
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#non-correlated-subqueries">
+ Non-correlated subqueries
+ </a>
+ </li>
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#scalar-subqueries">
+ Scalar subqueries
+ </a>
+ </li>
+ <li class="toc-h3 nav-item toc-entry">
+ <a class="reference internal nav-link" href="#non-scalar-subqueries">
+ Non-scalar subqueries
+ </a>
+ </li>
+ </ul>
</li>
</ul>
@@ -521,76 +649,378 @@
-->
<section id="subqueries">
<h1>Subqueries<a class="headerlink" href="#subqueries" title="Link to this
heading">¶</a></h1>
-<p>DataFusion supports <code class="docutils literal notranslate"><span
class="pre">EXISTS</span></code>, <code class="docutils literal
notranslate"><span class="pre">NOT</span> <span
class="pre">EXISTS</span></code>, <code class="docutils literal
notranslate"><span class="pre">IN</span></code>, <code class="docutils literal
notranslate"><span class="pre">NOT</span> <span class="pre">IN</span></code>
and Scalar Subqueries.</p>
-<p>The examples below are based on the following table.</p>
-<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">select</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">from</span><span class="w"> </span><span class="n">x</span><span
class="p">;</span>
+<p>Subqueries (also known as inner queries or nested queries) are queries
within
+a query.
+Subqueries can be used in <code class="docutils literal notranslate"><span
class="pre">SELECT</span></code>, <code class="docutils literal
notranslate"><span class="pre">FROM</span></code>, <code class="docutils
literal notranslate"><span class="pre">WHERE</span></code>, and <code
class="docutils literal notranslate"><span class="pre">HAVING</span></code>
clauses.</p>
+<p>The examples below are based on the following tables.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">FROM</span><span class="w"> </span><span class="n">x</span><span
class="p">;</span>
+
<span class="o">+</span><span class="c1">----------+----------+</span>
<span class="o">|</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">column_2</span><span class="w"> </span><span
class="o">|</span>
<span class="o">+</span><span class="c1">----------+----------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">1</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+----------+</span>
+<span class="o">|</span><span class="w"> </span><span class="mi">2</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="mi">4</span><span class="w"> </span><span class="o">|</span>
+<span class="o">+</span><span class="c1">----------+----------+</span>
+</pre></div>
+</div>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">FROM</span><span class="w"> </span><span class="n">y</span><span
class="p">;</span>
+
+<span class="o">+</span><span class="c1">--------+--------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="nb">number</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">string</span><span class="w"> </span><span
class="o">|</span>
+<span class="o">+</span><span class="c1">--------+--------+</span>
+<span class="o">|</span><span class="w"> </span><span class="mi">1</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="n">one</span><span class="w"> </span><span class="o">|</span>
+<span class="o">+</span><span class="c1">--------+--------+</span>
+<span class="o">|</span><span class="w"> </span><span class="mi">2</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="n">two</span><span class="w"> </span><span class="o">|</span>
+<span class="o">+</span><span class="c1">--------+--------+</span>
+<span class="o">|</span><span class="w"> </span><span class="mi">3</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="n">three</span><span class="w"> </span><span class="o">|</span>
+<span class="o">+</span><span class="c1">--------+--------+</span>
+<span class="o">|</span><span class="w"> </span><span class="mi">4</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="n">four</span><span class="w"> </span><span class="o">|</span>
+<span class="o">+</span><span class="c1">--------+--------+</span>
+</pre></div>
+</div>
+<section id="subquery-operators">
+<h2>Subquery operators<a class="headerlink" href="#subquery-operators"
title="Link to this heading">¶</a></h2>
+<ul class="simple">
+<li><p><a class="reference internal" href="#not-exists">[ NOT ]
EXISTS</a></p></li>
+<li><p><a class="reference internal" href="#not-in">[ NOT ] IN</a></p></li>
+</ul>
+<section id="not-exists">
+<h3>[ NOT ] EXISTS<a class="headerlink" href="#not-exists" title="Link to this
heading">¶</a></h3>
+<p>The <code class="docutils literal notranslate"><span
class="pre">EXISTS</span></code> operator returns all rows where a
+<em><a class="reference internal" href="#correlated-subqueries">correlated
subquery</a></em> produces one or more matches for
+that row. <code class="docutils literal notranslate"><span
class="pre">NOT</span> <span class="pre">EXISTS</span></code> returns all rows
where a <em>correlated subquery</em> produces
+zero matches for that row. Only <em>correlated subqueries</em> are
supported.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="p">[</span><span
class="k">NOT</span><span class="p">]</span><span class="w"> </span><span
class="k">EXISTS</span><span class="w"> </span><span class="p">(</span><span
class="n">subquery</span><span class="p">)</span>
+</pre></div>
+</div>
+</section>
+<section id="not-in">
+<h3>[ NOT ] IN<a class="headerlink" href="#not-in" title="Link to this
heading">¶</a></h3>
+<p>The <code class="docutils literal notranslate"><span
class="pre">IN</span></code> operator returns all rows where a given
expression’s value can be found
+in the results of a <em><a class="reference internal"
href="#correlated-subqueries">correlated subquery</a></em>.
+<code class="docutils literal notranslate"><span class="pre">NOT</span> <span
class="pre">IN</span></code> returns all rows where a given expression’s value
cannot be found in
+the results of a subquery or list of values.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="n">expression</span><span
class="w"> </span><span class="p">[</span><span class="k">NOT</span><span
class="p">]</span><span class="w"> </span><span class="k">IN</span><span
class="w"> </span><span class="p">(</span><span class="n">subquery</span><span
class="o">|</span><span class="n">list</span><span class="o">-</span><span
class="n">literal</span><span class="p">)</span>
</pre></div>
</div>
-<section id="exists">
-<h2>EXISTS<a class="headerlink" href="#exists" title="Link to this
heading">¶</a></h2>
-<p>The <code class="docutils literal notranslate"><span
class="pre">EXISTS</span></code> syntax can be used to find all rows in a
relation where a correlated subquery produces one or more matches
-for that row. Only correlated subqueries are supported.</p>
-<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">select</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">from</span><span class="w"> </span><span class="n">x</span><span
class="w"> </span><span class="n">y</span><span class="w"> </span><span
class="k">where</span><span class="w"> </span><span
class="k">exists</span><span class="w"> </span><span class="p">(</span><span
class="k">select</span><span [...]
+<section id="examples">
+<h4>Examples<a class="headerlink" href="#examples" title="Link to this
heading">¶</a></h4>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">FROM</span><span class="w"> </span><span class="n">x</span><span
class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span class="k">IN</span><span
class="w"> </span><span class="p">(</span><span class="mi">1</span><span c [...]
+
<span class="o">+</span><span class="c1">----------+----------+</span>
<span class="o">|</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">column_2</span><span class="w"> </span><span
class="o">|</span>
<span class="o">+</span><span class="c1">----------+----------+</span>
<span class="o">|</span><span class="w"> </span><span class="mi">1</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span>
<span class="o">+</span><span class="c1">----------+----------+</span>
-<span class="mi">1</span><span class="w"> </span><span
class="k">row</span><span class="w"> </span><span class="k">in</span><span
class="w"> </span><span class="k">set</span><span class="p">.</span>
+</pre></div>
+</div>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">FROM</span><span class="w"> </span><span class="n">x</span><span
class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span
class="k">NOT</span><span class="w"> </span><span class="k">IN</span><span
class="w"> </span><span [...]
+
+<span class="o">+</span><span class="c1">----------+----------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">column_2</span><span class="w"> </span><span
class="o">|</span>
+<span class="o">+</span><span class="c1">----------+----------+</span>
+<span class="o">|</span><span class="w"> </span><span class="mi">2</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="mi">4</span><span class="w"> </span><span class="o">|</span>
+<span class="o">+</span><span class="c1">----------+----------+</span>
</pre></div>
</div>
</section>
-<section id="not-exists">
-<h2>NOT EXISTS<a class="headerlink" href="#not-exists" title="Link to this
heading">¶</a></h2>
-<p>The <code class="docutils literal notranslate"><span class="pre">NOT</span>
<span class="pre">EXISTS</span></code> syntax can be used to find all rows in a
relation where a correlated subquery produces zero matches
-for that row. Only correlated subqueries are supported.</p>
-<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">select</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">from</span><span class="w"> </span><span class="n">x</span><span
class="w"> </span><span class="n">y</span><span class="w"> </span><span
class="k">where</span><span class="w"> </span><span class="k">not</span><span
class="w"> </span><span class="k">exists</span><span class="w"> </span><span
cla [...]
-<span class="mi">0</span><span class="w"> </span><span
class="k">rows</span><span class="w"> </span><span class="k">in</span><span
class="w"> </span><span class="k">set</span><span class="p">.</span>
+</section>
+</section>
+<section id="select-clause-subqueries">
+<h2>SELECT clause subqueries<a class="headerlink"
href="#select-clause-subqueries" title="Link to this heading">¶</a></h2>
+<p><code class="docutils literal notranslate"><span
class="pre">SELECT</span></code> clause subqueries use values returned from the
inner query as part
+of the outer query’s <code class="docutils literal notranslate"><span
class="pre">SELECT</span></code> list.
+The <code class="docutils literal notranslate"><span
class="pre">SELECT</span></code> clause only supports <a class="reference
internal" href="#scalar-subqueries">scalar subqueries</a> that
+return a single value per execution of the inner query.
+The returned value can be unique per row.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span><span
class="w"> </span><span class="p">[</span><span
class="n">expression1</span><span class="p">[,</span><span class="w">
</span><span class="n">expression2</span><span class="p">,</span><span
class="w"> </span><span class="p">...,</span><span class="w"> </span><span
class="n">expressionN</span><span class="p">],]</span><span class="w">
</span><span class="p">(</span><span class [...]
</pre></div>
</div>
+<p><strong>Note</strong>: <code class="docutils literal notranslate"><span
class="pre">SELECT</span></code> clause subqueries can be used as an
alternative to <code class="docutils literal notranslate"><span
class="pre">JOIN</span></code>
+operations.</p>
+<section id="example">
+<h3>Example<a class="headerlink" href="#example" title="Link to this
heading">¶</a></h3>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span>
+<span class="w"> </span><span class="n">column_1</span><span
class="p">,</span>
+<span class="w"> </span><span class="p">(</span>
+<span class="w"> </span><span class="k">SELECT</span>
+<span class="w"> </span><span class="n">first_value</span><span
class="p">(</span><span class="n">string</span><span class="p">)</span>
+<span class="w"> </span><span class="k">FROM</span>
+<span class="w"> </span><span class="n">y</span>
+<span class="w"> </span><span class="k">WHERE</span>
+<span class="w"> </span><span class="nb">number</span><span class="w">
</span><span class="o">=</span><span class="w"> </span><span
class="n">x</span><span class="p">.</span><span class="n">column_1</span>
+<span class="w"> </span><span class="p">)</span><span class="w"> </span><span
class="k">AS</span><span class="w"> </span><span class="ss">"numeric
string"</span>
+<span class="k">FROM</span>
+<span class="w"> </span><span class="n">x</span><span class="p">;</span>
+
+<span class="o">+</span><span class="c1">----------+----------------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="nb">numeric</span><span class="w"> </span><span
class="n">string</span><span class="w"> </span><span class="o">|</span>
+<span class="o">+</span><span class="c1">----------+----------------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="mi">1</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">one</span><span class="w">
</span><span class="o">|</span>
+<span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">two</span><span class="w">
</span><span class="o">|</span>
+<span class="o">+</span><span class="c1">----------+----------------+</span>
+</pre></div>
+</div>
+</section>
</section>
-<section id="in">
-<h2>IN<a class="headerlink" href="#in" title="Link to this heading">¶</a></h2>
-<p>The <code class="docutils literal notranslate"><span
class="pre">IN</span></code> syntax can be used to find all rows in a relation
where a given expression’s value can be found in the
-results of a correlated subquery.</p>
-<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">select</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">from</span><span class="w"> </span><span class="n">x</span><span
class="w"> </span><span class="k">where</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span class="k">in</span><span
class="w"> </span><span class="p">(</span><span class="k">select</span><sp [...]
+<section id="from-clause-subqueries">
+<h2>FROM clause subqueries<a class="headerlink" href="#from-clause-subqueries"
title="Link to this heading">¶</a></h2>
+<p><code class="docutils literal notranslate"><span
class="pre">FROM</span></code> clause subqueries return a set of results that
is then queried and
+operated on by the outer query.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span><span
class="w"> </span><span class="n">expression1</span><span
class="p">[,</span><span class="w"> </span><span
class="n">expression2</span><span class="p">,</span><span class="w">
</span><span class="p">...,</span><span class="w"> </span><span
class="n">expressionN</span><span class="p">]</span><span class="w">
</span><span class="k">FROM</span><span class="w"> </span><span clas [...]
+</pre></div>
+</div>
+<section id="id1">
+<h3>Example<a class="headerlink" href="#id1" title="Link to this
heading">¶</a></h3>
+<p>The following query returns the average of maximum values per room.
+The inner query returns the maximum value for each field from each room.
+The outer query uses the results of the inner query and returns the average
+maximum value for each field.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span>
+<span class="w"> </span><span class="n">column_2</span>
+<span class="k">FROM</span>
+<span class="w"> </span><span class="p">(</span>
+<span class="w"> </span><span class="k">SELECT</span>
+<span class="w"> </span><span class="o">*</span>
+<span class="w"> </span><span class="k">FROM</span>
+<span class="w"> </span><span class="n">x</span>
+<span class="w"> </span><span class="k">WHERE</span>
+<span class="w"> </span><span class="n">column_1</span><span class="w">
</span><span class="o">></span><span class="w"> </span><span
class="mi">1</span>
+<span class="w"> </span><span class="p">);</span>
+
+<span class="o">+</span><span class="c1">----------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="n">column_2</span><span class="w"> </span><span class="o">|</span>
+<span class="o">+</span><span class="c1">----------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="mi">4</span><span class="w"> </span><span class="o">|</span>
+<span class="o">+</span><span class="c1">----------+</span>
+</pre></div>
+</div>
+</section>
+</section>
+<section id="where-clause-subqueries">
+<h2>WHERE clause subqueries<a class="headerlink"
href="#where-clause-subqueries" title="Link to this heading">¶</a></h2>
+<p><code class="docutils literal notranslate"><span
class="pre">WHERE</span></code> clause subqueries compare an expression to the
result of the subquery
+and return <em>true</em> or <em>false</em>.
+Rows that evaluate to <em>false</em> or NULL are filtered from results.
+The <code class="docutils literal notranslate"><span
class="pre">WHERE</span></code> 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).</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span>
+<span class="w"> </span><span class="n">expression1</span><span
class="p">[,</span><span class="w"> </span><span
class="n">expression2</span><span class="p">,</span><span class="w">
</span><span class="p">...,</span><span class="w"> </span><span
class="n">expressionN</span><span class="p">]</span>
+<span class="k">FROM</span>
+<span class="w"> </span><span class="o"><</span><span
class="n">measurement</span><span class="o">></span>
+<span class="k">WHERE</span>
+<span class="w"> </span><span class="n">expression</span><span class="w">
</span><span class="k">operator</span><span class="w"> </span><span
class="p">(</span><span class="o"><</span><span
class="n">subquery</span><span class="o">></span><span class="p">)</span>
+</pre></div>
+</div>
+<p><strong>Note:</strong> <code class="docutils literal notranslate"><span
class="pre">WHERE</span></code> clause subqueries can be used as an alternative
to <code class="docutils literal notranslate"><span
class="pre">JOIN</span></code>
+operations.</p>
+<section id="id2">
+<h3>Examples<a class="headerlink" href="#id2" title="Link to this
heading">¶</a></h3>
+<section id="where-clause-with-scalar-subquery">
+<h4><code class="docutils literal notranslate"><span
class="pre">WHERE</span></code> clause with scalar subquery<a
class="headerlink" href="#where-clause-with-scalar-subquery" title="Link to
this heading">¶</a></h4>
+<p>The following query returns all rows with <code class="docutils literal
notranslate"><span class="pre">column_2</span></code> values above the average
+of all <code class="docutils literal notranslate"><span
class="pre">number</span></code> values in <code class="docutils literal
notranslate"><span class="pre">y</span></code>.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span>
+<span class="w"> </span><span class="o">*</span>
+<span class="k">FROM</span>
+<span class="w"> </span><span class="n">x</span>
+<span class="k">WHERE</span>
+<span class="w"> </span><span class="n">column_2</span><span class="w">
</span><span class="o">></span><span class="w"> </span><span
class="p">(</span>
+<span class="w"> </span><span class="k">SELECT</span>
+<span class="w"> </span><span class="k">AVG</span><span
class="p">(</span><span class="nb">number</span><span class="p">)</span>
+<span class="w"> </span><span class="k">FROM</span>
+<span class="w"> </span><span class="n">y</span>
+<span class="w"> </span><span class="p">);</span>
+
<span class="o">+</span><span class="c1">----------+----------+</span>
<span class="o">|</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">column_2</span><span class="w"> </span><span
class="o">|</span>
<span class="o">+</span><span class="c1">----------+----------+</span>
-<span class="o">|</span><span class="w"> </span><span class="mi">1</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span>
+<span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="mi">4</span><span class="w"> </span><span
class="o">|</span>
<span class="o">+</span><span class="c1">----------+----------+</span>
-<span class="mi">1</span><span class="w"> </span><span
class="k">row</span><span class="w"> </span><span class="k">in</span><span
class="w"> </span><span class="k">set</span><span class="p">.</span>
</pre></div>
</div>
</section>
-<section id="not-in">
-<h2>NOT IN<a class="headerlink" href="#not-in" title="Link to this
heading">¶</a></h2>
-<p>The <code class="docutils literal notranslate"><span class="pre">NOT</span>
<span class="pre">IN</span></code> syntax can be used to find all rows in a
relation where a given expression’s value can not be found in the
-results of a correlated subquery.</p>
-<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">select</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">from</span><span class="w"> </span><span class="n">x</span><span
class="w"> </span><span class="k">where</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span
class="k">not</span><span class="w"> </span><span class="k">in</span><span
class="w"> </span><span [...]
-<span class="mi">0</span><span class="w"> </span><span
class="k">rows</span><span class="w"> </span><span class="k">in</span><span
class="w"> </span><span class="k">set</span><span class="p">.</span>
+<section id="where-clause-with-non-scalar-subquery">
+<h4><code class="docutils literal notranslate"><span
class="pre">WHERE</span></code> clause with non-scalar subquery<a
class="headerlink" href="#where-clause-with-non-scalar-subquery" title="Link to
this heading">¶</a></h4>
+<p>Non-scalar subqueries must use the <code class="docutils literal
notranslate"><span class="pre">[NOT]</span> <span class="pre">IN</span></code>
or <code class="docutils literal notranslate"><span class="pre">[NOT]</span>
<span class="pre">EXISTS</span></code> operators and
+can only return a single column.
+The values in the returned column are evaluated as a list.</p>
+<p>The following query returns all rows with <code class="docutils literal
notranslate"><span class="pre">column_2</span></code> values in table <code
class="docutils literal notranslate"><span class="pre">x</span></code> that
+are in the list of numbers with string lengths greater than three from table
+<code class="docutils literal notranslate"><span
class="pre">y</span></code>.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span>
+<span class="w"> </span><span class="o">*</span>
+<span class="k">FROM</span>
+<span class="w"> </span><span class="n">x</span>
+<span class="k">WHERE</span>
+<span class="w"> </span><span class="n">column_2</span><span class="w">
</span><span class="k">IN</span><span class="w"> </span><span class="p">(</span>
+<span class="w"> </span><span class="k">SELECT</span>
+<span class="w"> </span><span class="nb">number</span>
+<span class="w"> </span><span class="k">FROM</span>
+<span class="w"> </span><span class="n">y</span>
+<span class="w"> </span><span class="k">WHERE</span>
+<span class="w"> </span><span class="k">length</span><span
class="p">(</span><span class="n">string</span><span class="p">)</span><span
class="w"> </span><span class="o">></span><span class="w"> </span><span
class="mi">3</span>
+<span class="w"> </span><span class="p">);</span>
+
+<span class="o">+</span><span class="c1">----------+----------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">column_2</span><span class="w"> </span><span
class="o">|</span>
+<span class="o">+</span><span class="c1">----------+----------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="mi">4</span><span class="w"> </span><span
class="o">|</span>
+<span class="o">+</span><span class="c1">----------+----------+</span>
</pre></div>
</div>
</section>
-<section id="scalar-subquery">
-<h2>Scalar Subquery<a class="headerlink" href="#scalar-subquery" title="Link
to this heading">¶</a></h2>
-<p>A scalar subquery can be used to produce a single value that can be used in
many different contexts in a query. Here
-is an example of a filter using a scalar subquery. Only correlated subqueries
are supported.</p>
-<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">select</span><span
class="w"> </span><span class="o">*</span><span class="w"> </span><span
class="k">from</span><span class="w"> </span><span class="n">x</span><span
class="w"> </span><span class="n">y</span><span class="w"> </span><span
class="k">where</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span
class="o"><</span><span class="w"> </span><span [...]
+</section>
+<section id="where-clause-with-correlated-subquery">
+<h3><code class="docutils literal notranslate"><span
class="pre">WHERE</span></code> clause with correlated subquery<a
class="headerlink" href="#where-clause-with-correlated-subquery" title="Link to
this heading">¶</a></h3>
+<p>The following query returns rows with <code class="docutils literal
notranslate"><span class="pre">column_2</span></code> values from table <code
class="docutils literal notranslate"><span class="pre">x</span></code> greater
+than the average <code class="docutils literal notranslate"><span
class="pre">string</span></code> value length from table <code class="docutils
literal notranslate"><span class="pre">y</span></code>.
+The subquery in the <code class="docutils literal notranslate"><span
class="pre">WHERE</span></code> clause uses the <code class="docutils literal
notranslate"><span class="pre">column_1</span></code> value from the outer
+query to return the average <code class="docutils literal notranslate"><span
class="pre">string</span></code> value length for that specific value.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span>
+<span class="w"> </span><span class="o">*</span>
+<span class="k">FROM</span>
+<span class="w"> </span><span class="n">x</span>
+<span class="k">WHERE</span>
+<span class="w"> </span><span class="n">column_2</span><span class="w">
</span><span class="o">></span><span class="w"> </span><span
class="p">(</span>
+<span class="w"> </span><span class="k">SELECT</span>
+<span class="w"> </span><span class="k">AVG</span><span
class="p">(</span><span class="k">length</span><span class="p">(</span><span
class="n">string</span><span class="p">))</span>
+<span class="w"> </span><span class="k">FROM</span>
+<span class="w"> </span><span class="n">y</span>
+<span class="w"> </span><span class="k">WHERE</span>
+<span class="w"> </span><span class="nb">number</span><span class="w">
</span><span class="o">=</span><span class="w"> </span><span
class="n">x</span><span class="p">.</span><span class="n">column_1</span>
+<span class="w"> </span><span class="p">);</span>
+
<span class="o">+</span><span class="c1">----------+----------+</span>
<span class="o">|</span><span class="w"> </span><span
class="n">column_1</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">column_2</span><span class="w"> </span><span
class="o">|</span>
<span class="o">+</span><span class="c1">----------+----------+</span>
-<span class="o">|</span><span class="w"> </span><span class="mi">1</span><span
class="w"> </span><span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span>
+<span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="mi">4</span><span class="w"> </span><span
class="o">|</span>
<span class="o">+</span><span class="c1">----------+----------+</span>
-<span class="mi">1</span><span class="w"> </span><span
class="k">row</span><span class="w"> </span><span class="k">in</span><span
class="w"> </span><span class="k">set</span><span class="p">.</span>
</pre></div>
</div>
</section>
+</section>
+<section id="having-clause-subqueries">
+<h2>HAVING clause subqueries<a class="headerlink"
href="#having-clause-subqueries" title="Link to this heading">¶</a></h2>
+<p><code class="docutils literal notranslate"><span
class="pre">HAVING</span></code> clause subqueries compare an expression that
uses aggregate values
+returned by aggregate functions in the <code class="docutils literal
notranslate"><span class="pre">SELECT</span></code> clause to the result of the
+subquery and return <em>true</em> or <em>false</em>.
+Rows that evaluate to <em>false</em> are filtered from results.
+The <code class="docutils literal notranslate"><span
class="pre">HAVING</span></code> 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).</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span>
+<span class="w"> </span><span class="n">aggregate_expression1</span><span
class="p">[,</span><span class="w"> </span><span
class="n">aggregate_expression2</span><span class="p">,</span><span class="w">
</span><span class="p">...,</span><span class="w"> </span><span
class="n">aggregate_expressionN</span><span class="p">]</span>
+<span class="k">FROM</span>
+<span class="w"> </span><span class="o"><</span><span
class="n">measurement</span><span class="o">></span>
+<span class="k">WHERE</span>
+<span class="w"> </span><span class="o"><</span><span
class="n">conditional_expression</span><span class="o">></span>
+<span class="k">GROUP</span><span class="w"> </span><span class="k">BY</span>
+<span class="w"> </span><span class="n">column_expression1</span><span
class="p">[,</span><span class="w"> </span><span
class="n">column_expression2</span><span class="p">,</span><span class="w">
</span><span class="p">...,</span><span class="w"> </span><span
class="n">column_expressionN</span><span class="p">]</span>
+<span class="k">HAVING</span>
+<span class="w"> </span><span class="n">expression</span><span class="w">
</span><span class="k">operator</span><span class="w"> </span><span
class="p">(</span><span class="o"><</span><span
class="n">subquery</span><span class="o">></span><span class="p">)</span>
+</pre></div>
+</div>
+<section id="id3">
+<h3>Examples<a class="headerlink" href="#id3" title="Link to this
heading">¶</a></h3>
+<p>The following query calculates the averages of even and odd numbers in
table <code class="docutils literal notranslate"><span
class="pre">y</span></code>
+and returns the averages that are equal to the maximum value of <code
class="docutils literal notranslate"><span class="pre">column_1</span></code>
+in table <code class="docutils literal notranslate"><span
class="pre">x</span></code>.</p>
+<section id="having-clause-with-a-scalar-subquery">
+<h4><code class="docutils literal notranslate"><span
class="pre">HAVING</span></code> clause with a scalar subquery<a
class="headerlink" href="#having-clause-with-a-scalar-subquery" title="Link to
this heading">¶</a></h4>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span>
+<span class="w"> </span><span class="k">AVG</span><span
class="p">(</span><span class="nb">number</span><span class="p">)</span><span
class="w"> </span><span class="k">AS</span><span class="w"> </span><span
class="k">avg</span><span class="p">,</span>
+<span class="w"> </span><span class="p">(</span><span
class="nb">number</span><span class="w"> </span><span class="o">%</span><span
class="w"> </span><span class="mi">2</span><span class="w"> </span><span
class="o">=</span><span class="w"> </span><span class="mi">0</span><span
class="p">)</span><span class="w"> </span><span class="k">AS</span><span
class="w"> </span><span class="n">even</span>
+<span class="k">FROM</span>
+<span class="w"> </span><span class="n">y</span>
+<span class="k">GROUP</span><span class="w"> </span><span class="k">BY</span>
+<span class="w"> </span><span class="n">even</span>
+<span class="k">HAVING</span>
+<span class="w"> </span><span class="k">avg</span><span class="w">
</span><span class="o">=</span><span class="w"> </span><span class="p">(</span>
+<span class="w"> </span><span class="k">SELECT</span>
+<span class="w"> </span><span class="k">MAX</span><span
class="p">(</span><span class="n">column_1</span><span class="p">)</span>
+<span class="w"> </span><span class="k">FROM</span>
+<span class="w"> </span><span class="n">x</span>
+<span class="w"> </span><span class="p">);</span>
+
+<span class="o">+</span><span class="c1">-------+--------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="k">avg</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">even</span><span class="w"> </span><span
class="o">|</span>
+<span class="o">+</span><span class="c1">-------+--------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="k">false</span><span class="w"> </span><span
class="o">|</span>
+<span class="o">+</span><span class="c1">-------+--------+</span>
+</pre></div>
+</div>
+</section>
+<section id="having-clause-with-a-non-scalar-subquery">
+<h4><code class="docutils literal notranslate"><span
class="pre">HAVING</span></code> clause with a non-scalar subquery<a
class="headerlink" href="#having-clause-with-a-non-scalar-subquery" title="Link
to this heading">¶</a></h4>
+<p>Non-scalar subqueries must use the <code class="docutils literal
notranslate"><span class="pre">[NOT]</span> <span class="pre">IN</span></code>
or <code class="docutils literal notranslate"><span class="pre">[NOT]</span>
<span class="pre">EXISTS</span></code> operators and
+can only return a single column.
+The values in the returned column are evaluated as a list.</p>
+<p>The following query calculates the averages of even and odd numbers in
table <code class="docutils literal notranslate"><span
class="pre">y</span></code>
+and returns the averages that are in <code class="docutils literal
notranslate"><span class="pre">column_1</span></code> of table <code
class="docutils literal notranslate"><span class="pre">x</span></code>.</p>
+<div class="highlight-sql notranslate"><div
class="highlight"><pre><span></span><span class="k">SELECT</span>
+<span class="w"> </span><span class="k">AVG</span><span
class="p">(</span><span class="nb">number</span><span class="p">)</span><span
class="w"> </span><span class="k">AS</span><span class="w"> </span><span
class="k">avg</span><span class="p">,</span>
+<span class="w"> </span><span class="p">(</span><span
class="nb">number</span><span class="w"> </span><span class="o">%</span><span
class="w"> </span><span class="mi">2</span><span class="w"> </span><span
class="o">=</span><span class="w"> </span><span class="mi">0</span><span
class="p">)</span><span class="w"> </span><span class="k">AS</span><span
class="w"> </span><span class="n">even</span>
+<span class="k">FROM</span>
+<span class="w"> </span><span class="n">y</span>
+<span class="k">GROUP</span><span class="w"> </span><span class="k">BY</span>
+<span class="w"> </span><span class="n">even</span>
+<span class="k">HAVING</span>
+<span class="w"> </span><span class="k">avg</span><span class="w">
</span><span class="k">IN</span><span class="w"> </span><span class="p">(</span>
+<span class="w"> </span><span class="k">SELECT</span>
+<span class="w"> </span><span class="n">column_1</span>
+<span class="w"> </span><span class="k">FROM</span>
+<span class="w"> </span><span class="n">x</span>
+<span class="w"> </span><span class="p">);</span>
+
+<span class="o">+</span><span class="c1">-------+--------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="k">avg</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="n">even</span><span class="w"> </span><span
class="o">|</span>
+<span class="o">+</span><span class="c1">-------+--------+</span>
+<span class="o">|</span><span class="w"> </span><span
class="mi">2</span><span class="w"> </span><span class="o">|</span><span
class="w"> </span><span class="k">false</span><span class="w"> </span><span
class="o">|</span>
+<span class="o">+</span><span class="c1">-------+--------+</span>
+</pre></div>
+</div>
+</section>
+</section>
+</section>
+<section id="subquery-categories">
+<h2>Subquery categories<a class="headerlink" href="#subquery-categories"
title="Link to this heading">¶</a></h2>
+<p>Subqueries can be categorized as one or more of the following based on the
+behavior of the subquery:</p>
+<ul class="simple">
+<li><p><a class="reference internal"
href="#correlated-subqueries">correlated</a> or
+<a class="reference internal"
href="#non-correlated-subqueries">non-correlated</a></p></li>
+<li><p><a class="reference internal" href="#scalar-subqueries">scalar</a> or
<a class="reference internal"
href="#non-scalar-subqueries">non-scalar</a></p></li>
+</ul>
+<section id="correlated-subqueries">
+<h3>Correlated subqueries<a class="headerlink" href="#correlated-subqueries"
title="Link to this heading">¶</a></h3>
+<p>In a <strong>correlated</strong> subquery, the inner query depends on the
values of the
+current row being processed.</p>
+<p><strong>Note:</strong> DataFusion internally rewrites correlated subqueries
into JOINs to
+improve performance. In general correlated subqueries are <strong>less
performant</strong>
+than non-correlated subqueries.</p>
+</section>
+<section id="non-correlated-subqueries">
+<h3>Non-correlated subqueries<a class="headerlink"
href="#non-correlated-subqueries" title="Link to this heading">¶</a></h3>
+<p>In a <strong>non-correlated</strong> subquery, the inner query
<em>doesn’t</em> depend on the outer
+query and executes independently.
+The inner query executes first, and then passes the results to the outer
query.</p>
+</section>
+<section id="scalar-subqueries">
+<h3>Scalar subqueries<a class="headerlink" href="#scalar-subqueries"
title="Link to this heading">¶</a></h3>
+<p>A <strong>scalar</strong> subquery returns a single value (one column of
one row).
+If no rows are returned, the subquery returns NULL.</p>
+</section>
+<section id="non-scalar-subqueries">
+<h3>Non-scalar subqueries<a class="headerlink" href="#non-scalar-subqueries"
title="Link to this heading">¶</a></h3>
+<p>A <strong>non-scalar</strong> 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.</p>
+</section>
+</section>
</section>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
