SQL Server Comparison Operator Modifier ALL does not work?

Question

It seems that Comparison Operator Modifier ALL does not work as expected. I am using Microsoft SQL Server 2008 R2 Management Studio (v10.50.4000.0).

declare @AllTest table 
(
    ID int identity,
    Crew int,
    Iteration int,
    Value varchar(200)
)

insert @AllTest
values
    (1, 1, 'a'),
    (2, 1, 'b'),
    (3, 1, NULL),
    (1, 2, 'd'),
    (1, 3, 'e'),
    (3, 2, NULL),
    (2, 2, 'a'),
    (2, 3, 'b'),
    (1, 4, NULL),
    (1, 5, 'f')

select
    *
from 
    @AllTest
where 
    1 = 1
    and Crew = 1
    and Value is not NULL   

select
    *
from
    @AllTest
where 
    1 = 1
    and Crew = 1
    and Value is not NULL
    and Iteration = all(select v from (values (1),(2),(3)) as t(v))

select
    *
from
    @AllTest
where
    1 = 1
    and Crew = 1
    and Value is not NULL
    and Iteration != all(select v from (values (1),(2),(3)) as t(v))

The query where 'Iteration = all' does not return any results but it should. The query where 'Iteration != all' does work as expected. (However it seems that such a result would be more easily achieved by 'Iteration not in ' and without the need for a kind of subquery using something like a Table Value Constructor or union to present the values.)

Anyway, it seems really weird that ALL does not work as shown and that a simple expression such as 'Iteration = all(1,2,3)' or 'Iteration != all(1,2,3)' is invalid!

select
    *
from 
    @AllTest
where 
    1 = 1
    and Crew = 1
    and Value is not NULL
    and Iteration != all(1,2,3)

Is this a problem with my version of SQL Server or what am I missing?

If ALL doesn't work then what is the best alternative way to construct the query to behave and return the result which should be produced using ALL?

EDIT: I apologize for not making my question clearer in regards to expected result. I am looking for a way to only have a result returned when there are rows with Value NULL and Iteration equal to 1 and 2 and 3 with no missing rows.

I hope that makes sense.

Also, I see how 'Iteration = all' is looking for a single row, I have tried 'Iteration in all' which would make more sense semantically but it is considered incorrect syntax.

Answer 1

Iteration = all(select v from (values (1),(2),(3)) as t(v))

means

Iteration = 1 AND Iteration = 2 AND Iteration = 3

WHERE operates on a row at a time. It is impossible for the above to be true for any single row - hence no results.

---

Following your edit one way of getting your desired behaviour is

;WITH CTE
     AS (SELECT *
         FROM   @AllTest
         WHERE  Crew = 1
                AND Value IS NOT NULL)
SELECT *
FROM   CTE
WHERE  NOT EXISTS (SELECT v
                   FROM   (VALUES (1),
                                  (2),
                                  (3)) AS t(v)
                   EXCEPT
                   SELECT c2.Iteration
                   FROM   cte c2)