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.