Subqueries are queries inside a larger query. Their results are evaluated first, and then
used as either single values or, if they return a number of values, as a set. (Since tables are
sets, and since subqueries return sets, you can use subqueries anyhwere you could use a
table.) The two examples here both return a single value. There are two principal types of
subqueries:
Simple subquery: This type of subquery is not dependent on the outer query. Simple
subqueries are faster. Since their value does not change for each row of the outer
query, they are evaluated only once.
Correlated subquery: The subqueries in the example are the more complicated varieties,
called correlated subqueries. The subqueries are called for each row of the
outer query because their value depends on the outer query. As a result, they are calculated
again for each row of the outer query.
Both subqueries search for rows that occur in the same week and year as the outer
query, whose gross is negative (in other words, which are purchases and not income),
and that are completed transactions (as opposed to expired transactions or temporary
authorizations).
Pages:
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219