In SQL Server, the ALL
operator can be used with a subquery to compare a scalar value with a single-column set of values returned by the subquery.
It’s also true that the SELECT
clause and UNION
operator both accept an ALL
argument, although this usage has a different purpose (allows duplicates in the result set).
Below are examples of using the ALL
operator with a subquery.
Example
Assume we have two tables; Cats
and Dogs
Cats
+---------+-----------+ | CatId | CatName | |---------+-----------| | 1 | Meow | | 2 | Fluffy | | 3 | Scratch | +---------+-----------+
Dogs
+---------+-----------+ | DogId | DogName | |---------+-----------| | 1 | Fetch | | 2 | Fluffy | | 3 | Wag | +---------+-----------+
Now let’s run a subquery using the ALL
operator.
SELECT
CatId,
CatName
FROM Cats c
WHERE c.CatName = ALL (SELECT DogName FROM Dogs);
Result:
(0 rows affected)
In this case, no rows were returned. This is because ALL
requires the scalar expression to compare positively to every value that is returned by the subquery.
In this case, the subquery was so broad that all rows from the Dogs
table was returned. This would require that each dog had at least one corresponding cat with the same name.
Let’s change the subquery slightly.
SELECT
CatId,
CatName
FROM Cats c
WHERE c.CatName = ALL (
SELECT DogName FROM Dogs
WHERE DogId = 2
);
Result:
+---------+-----------+ | CatId | CatName | |---------+-----------| | 2 | Fluffy | +---------+-----------+
In this case I get a positive result, because all rows returned by the subquery had a corresponding row in the Cats
table (albeit only one row).
Return the Opposite
We can use any comparison operator with ALL
. So we could modify the previous examples to return the opposite result, simply by changing the equals operator (=) to a not equal to operator (either <>
or the non ISO standard !=
).
SELECT
CatId,
CatName
FROM Cats c
WHERE c.CatName <> ALL (SELECT DogName FROM Dogs);
Result:
+---------+-----------+ | CatId | CatName | |---------+-----------| | 1 | Meow | | 3 | Scratch | +---------+-----------+
So instead of returning all rows that have a corresponding row in the subquery, we return all rows that don’t have a corresponding row.
And we can do the same thing with the other example.
SELECT
CatId,
CatName
FROM Cats c
WHERE c.CatName <> ALL (
SELECT DogName FROM Dogs
WHERE DogId = 2
);
Result:
+---------+-----------+ | CatId | CatName | |---------+-----------| | 1 | Meow | | 3 | Scratch | +---------+-----------+
Error 116?
If you get error 116 when using ALL
, it’s probably because you’re selecting multiple columns in your subquery. The ALL
operator can only be used with subqueries that have a result set of one column.
Here’s an example of how we can cause this error.
SELECT
CatId,
CatName
FROM Cats c
WHERE c.CatName = ALL (SELECT DogId, DogName FROM Dogs);
I simply added a column to the subquery.
It’s a common error when using the wildcard operator to select all columns in the subquery.
SELECT
CatId,
CatName
FROM Cats c
WHERE c.CatName = ALL (SELECT * FROM Dogs);
Either way, the result is the same:
Msg 116, Level 16, State 1, Line 5 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.