3 Ways to use ALL in SQL Server

You might be familiar with the ALL option in SQL Server. Perhaps you’ve used it along with the UNION operator to include any duplicates that might be returned in the result set.

But did you know that ALL can also be used in two other contexts?

ALL can be used in the following three contexts:

As an argument to the SELECT clause.
As an argument to the UNION clause.
As a logical operator when comparing a scalar value with a single-column set of values.

Examples of each of these contexts follows.

`ALL` in the `SELECT` Clause

When used with the SELECT clause, ALL specifies that duplicate values are returned in the result set.

You probably already use this implicitly without even knowing.

In T-SQL, the syntax for the SELECT clause goes like this:

SELECT [ ALL | DISTINCT ]  
[ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ]   
<select_list>   
<select_list> ::=   
    {   
      *   
      | { table_name | view_name | table_alias }.*   
      | {  
          [ { table_name | view_name | table_alias }. ]  
               { column_name | $IDENTITY | $ROWGUID }   
          | udt_column_name [ { . | :: } { { property_name | field_name }   
            | method_name ( argument [ ,...n] ) } ]  
          | expression  
          [ [ AS ] column_alias ]   
         }  
      | column_alias = expression   
    } [ ,...n ]

The part that goes [ ALL | DISTINCT ] means that you get a choice between ALL and DISTINCT.

The square brackets means that this part is optional.

ALL specifies that duplicate rows can appear in the result set.
DISTINCT specifies that only unique rows can appear in the result set.

ALL is the default value, so if you don’t specify ALL or DISTINCT, ALL is used.

Example

So the following two statements are equivalent:

SELECT DogName 
FROM Dogs;

SELECT ALL DogName 
FROM Dogs;

Example result:

+-----------+
| DogName   |
|-----------|
| Fetch     |
| Fluffy    |
| Wag       |
| Fetch     |
+-----------+
(4 rows affected)
+-----------+
| DogName   |
|-----------|
| Fetch     |
| Fluffy    |
| Wag       |
| Fetch     |
+-----------+
(4 rows affected)

Both results show that there are two dogs named “Fetch”.

If we swap the ALL argument for DISTINCT, only one row will be returned for “Fetch”. This is because DISTINCT removes any duplicate values from the result set.

SELECT DISTINCT DogName 
FROM Dogs;

Example result:

+-----------+
| DogName   |
|-----------|
| Fetch     |
| Fluffy    |
| Wag       |
+-----------+
(3 rows affected)

`ALL` in the `UNION` Clause

ALL does the same thing when used with the UNION clause. It specifies that duplicate values are returned in the result set.

But obviously, UNION is a different clause to SELECT, so the context is slightly different.

The UNION clause concatenates the results of two queries into a single result set. You can use it with or without the ALL argument:

UNION ALL – Includes duplicates.
UNION – Excludes duplicates.

Example

Here’s an example of using UNION ALL to combine two queries.

Let’s add a table called Cats. So we have two tables: Dogs and Cats

Dogs

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 1       | Fetch     |
| 2       | Fluffy    |
| 3       | Wag       |
| 1002    | Fetch     |
+---------+-----------+

Cats

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 1       | Meow      |
| 2       | Fluffy    |
| 3       | Scratch   |
+---------+-----------+

Now let’s select the dog/cat name from each table and use UNION ALL to combine the results from both tables.

SELECT DogName AS PetName
FROM Dogs
UNION ALL
SELECT CatName
FROM Cats;

Result:

+-----------+
| PetName   |
|-----------|
| Fetch     |
| Fluffy    |
| Wag       |
| Fetch     |
| Meow      |
| Fluffy    |
| Scratch   |
+-----------+
(7 rows affected)

In this case, seven rows are returned. We can see that “Fetch” is returned twice. This is because there are two dogs named Fetch.

There’s also a cat and a dog with the same name: Fluffy. (We know the other one’s a cat because there was only one dog called Fluffy in the previous example).

Let’s see what happens when I remove the ALL argument.

SELECT DogName AS PetName
FROM Dogs
UNION
SELECT CatName
FROM Cats;

Result:

+-----------+
| PetName   |
|-----------|
| Fetch     |
| Fluffy    |
| Meow      |
| Scratch   |
| Wag       |
+-----------+
(5 rows affected)

This time only five rows are returned. Both duplicates are removed.

Note that this is different to applying DISTINCT to each individual SELECT statement. If we’d done that, Fluffy would have been returned twice, because the ALL would only apply for the SELECT statement that it’s being applied against (not to the concatenated results).

Here’s an example to illustrate what I mean.

SELECT DISTINCT DogName AS PetName
FROM Dogs
UNION ALL
SELECT DISTINCT CatName
FROM Cats;

Result:

+-----------+
| PetName   |
|-----------|
| Fetch     |
| Fluffy    |
| Wag       |
| Fluffy    |
| Meow      |
| Scratch   |
+-----------+
(6 rows affected)

The `ALL` Operator

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.

Example

As a refresher, here are our two tables:

Dogs

+---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 1       | Fetch     |
| 2       | Fluffy    |
| 3       | Wag       |
| 1002    | Fetch     |
+---------+-----------+

Cats

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 1       | Meow      |
| 2       | Fluffy    |
| 3       | Scratch   |
+---------+-----------+

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    |
+---------+-----------+

This time I get a positive result, because all rows returned by the subquery had a corresponding row in the Cats table (in this case, only one row).

ALL in the SELECT Clause

Example

ALL in the UNION Clause

Example

The ALL Operator

Example

`ALL` in the `SELECT` Clause

`ALL` in the `UNION` Clause

The `ALL` Operator