Multiple Rows, Single Column Subqueries - Building Conditions With Operators.

Multiple Rows, Single Column Subqueries - Building Conditions With Operators.

In my previous introductory article on subqueries, I explained that if a subquery returns multiple rows, then it cannot be used on either side of the equation condition. Fortunately, there are four additional operators that you can use to create conditions for these types of subqueries.

1. The IN operator

While it is not possible to equate a single value to a set of values, it is possible to determine whether a single value exists in a set of values. The following example shows how to create a condition that searches for a value within a set of values using the IN operator:

Capture4.PNG

2. The NOT IN operator

You can as well check if a value does not exist within a set of values using the NOT IN operator. Here’s a version of the previous query using the NOT IN operator:

Capture5.PNG

3. The ALL operator

While the IN operator checks to see if an expression can be found within a set of expressions, the ALL operator compares a single value to every value in the set. To create such a condition, you'll need to combine one of the comparison operators (=, <>, <, >, etc.) with the ALL operator. This query, for example, finds customers who have never made 15 payments.

Capture6.PNG

4. The ANY operator

ANY, like the ALL operator, allows you to compare a single value to members of a set of values; however, the condition returns true only as soon as a single comparison yields true. The example below retrieves the sum of payments for a customer whose last name is either JEFFREY, SMITH, or DAVIS. For this case, there is no customer whose last name is JEFFREY, but the statement still yields results because at least a single value comparison returned true for the set of values compared to. Using the ALL operator would return an empty set for this case.

Capture7.PNG

Wrap Up

To conclude, I'd like to point out that when comparing a value to a set of values using NOT IN or <> ALL, you must be careful that the set of values does not contain a NULL value because the server equates the value on the lefthand side of the expression to each member of the set, and any attempt to equate a value to NULL yields unknown.

Additionally, although most people prefer the IN operator, using = ANY is the same as using the IN operator.