/
subquery-expressions.rst
155 lines (103 loc) · 4.08 KB
/
subquery-expressions.rst
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
.. highlight:: psql
.. _sql_subquery_expressions:
Subquery expressions
====================
Some :ref:`operators <gloss-operator>` can be used with an :ref:`uncorrelated
subquery <gloss-uncorrelated-subquery>` to form a *subquery expression* that
returns a boolean value (i.e., ``true`` or ``false``) or ``NULL``.
.. SEEALSO::
:ref:`SQL: Value expressions <sql-scalar-subquery>`
.. rubric:: Table of contents
.. contents::
:local:
.. _sql_in_subquery_expression:
``IN (subquery)``
-----------------
Syntax:
.. code-block:: sql
expression IN (subquery)
The ``subquery`` must produce result rows with a single column only.
Here's an example::
cr> select name, surname, sex from employees
... where dept_id in (select id from departments where name = 'Marketing')
... order by name, surname;
+--------+----------+-----+
| name | surname | sex |
+--------+----------+-----+
| David | Bowe | M |
| David | Limb | M |
| Sarrah | Mcmillan | F |
| Smith | Clark | M |
+--------+----------+-----+
SELECT 4 rows in set (... sec)
The ``IN`` :ref:`operator <gloss-operator>` returns ``true`` if any
:ref:`subquery <gloss-subquery>` row equals the left-hand :ref:`operand
<gloss-operand>`. Otherwise, it returns ``false`` (including the case where the
subquery returns no rows).
The operator returns ``NULL`` if:
- The left-hand expression :ref:`evaluates <gloss-evaluation>` to ``NULL``
- There are no matching right-hand values and at least one right-hand value is
``NULL``
.. NOTE::
``IN (subquery)`` is an alias for ``= ANY (subquery)``
.. _sql_any_subquery_expression:
``ANY/SOME (subquery)``
-----------------------
Syntax:
.. code-block:: sql
expression comparison ANY | SOME (subquery)
Here, ``comparison`` can be any :ref:`basic comparison operator
<comparison-operators-basic>`. The ``subquery`` must produce result rows with a
single column only.
Here's an example::
cr> select name, population from countries
... where population > any (select * from unnest([8000000, 22000000, NULL]))
... order by population, name;
+--------------+------------+
| name | population |
+--------------+------------+
| Austria | 8747000 |
| South Africa | 55910000 |
| France | 66900000 |
| Turkey | 79510000 |
| Germany | 82670000 |
+--------------+------------+
SELECT 5 rows in set (... sec)
The ``ANY`` :ref:`operator <gloss-operator>` returns ``true`` if the defined
comparison is ``true`` for any of the result rows of the right-hand
:ref:`subquery <gloss-subquery>`.
The operator returns ``false`` if the comparison returns ``false`` for all
result rows of the subquery or if the subquery returns no rows.
The operator returns ``NULL`` if:
- The left-hand expression :ref:`evaluates <gloss-evaluation>` to ``NULL``
- There are no matching right-hand values and at least one right-hand value is
``NULL``
.. NOTE::
The following is not supported:
- ``IS NULL`` or ``IS NOT NULL`` as ``comparison``
- Matching as many columns as there are expressions on the left-hand row
e.g. ``(x,y) = ANY (select x, y from t)``
``ALL (subquery)``
------------------
Syntax:
.. code-block:: sql
value comparison ALL (subquery)
Here, ``comparison`` can be any :ref:`basic comparison operator
<comparison-operators-basic>`. The ``subquery`` must produce result rows with a
single column only.
Here's an example::
cr> select 100 <> ALL (select height from sys.summits) AS x;
+------+
| x |
+------+
| TRUE |
+------+
SELECT 1 row in set (... sec)
The ``ALL`` :ref:`operator <gloss-operator>` returns ``true`` if the defined
comparison is ``true`` for all of the result rows of the right-hand
:ref:`subquery <gloss-subquery>`.
The operator returns ``false`` if the comparison returns ``false`` for any
result rows of the subquery.
The operator returns ``NULL`` if:
- The left-hand expression :ref:`evaluates <gloss-evaluation>` to ``NULL``
- No comparison returns ``false`` and at least one right-hand value is ``NULL``