Скачать в pdf «PostgreSQL»

Subqueries as Lists of Values

The previous subqueries returned one row of data to the upper query. If any of the previous subqueries returned more than one row, an error would be generated: ERROR: More than one tuple returned by a subselect used as an expression. It is possible, however, to have subqueries return multiple rows.

Normal comparison operators like equal and less-than expect a single value on the left and on the right. For example, equality expects one value on the left of the equals sign (=) and one on the right—for example, col = 3. Two special comparisons, IN and not in, allow multiple values to appear on the right side. For example, the test col IN (1,2,3,4) compares col against four values. If col equals any of the four values, the comparison will return true and output the row. The test col NOT IN (1,2,3,4) will return true if col does not equal any of the four values.

You can specify an unlimited number of values on the right side of an IN or NOT IN comparison. More importantly, a subquery (instead of a constant) can be placed on the right side. It can then return multiple rows. The subquery is evaluated, and its output used like a list of constant values.

Suppose we want all employees who took sales orders on a certain date. We could perform this query in two ways. We could join the employee and salesorder tables, as shown in the first query of Figure
8.10. Alternatively, we could use a subquery, as shown in the second query. In this case, the subquery is evaluated and generates a list of values used by IN to perform the comparison. The subquery is possible because the salesorder table is involved in a single join, and the query does not return any columns from the salesorder table.

Скачать в pdf «PostgreSQL»