Test ALL rows exists

Question

It is very simple to test when row exists or not.

if exists(select * from dbo.APQP_head where zestaw=@zestaw)

I want to test in my query whether all rows satisfy the condition.

I need use some query like this

if All exists(select * from dbo.APQP_head where zestaw=@zestaw and type=3)

But this syntax is not correct.

Answer 1

if NOT exists(select * from dbo.APQP_head where zestaw<>@zestaw OR type<>3) 
  --all rows satisfy the condition

if your columns can be nullable, then

if NOT exists(select * from dbo.APQP_head where zestaw<>@zestaw OR type<>3) 
AND NOT exists(select * from dbo.APQP_head where zestaw IS NULL OR type IS NULL) 

Answer 2

This may perform better than an OR because it keep the AND and uses semi-joins

IF NOT EXISTS (
   SELECT zestaw, [type] FROM #foo
   EXCEPT
   SELECT zestaw, [type] FROM #foo where zestaw=@zestaw and type=3
   )
  -- all rows etc

Edit, quick and dirty test (SQL Server 2008 R2 Express on workstation), the EXCEPT uses more IO (2 touches) but less CPU (more efficient plan)

If you replace @zestaw with a constant, the NOT EXISTS .. OR .. wins

CREATE TABLE excepttest (zestaw int, [type] int);
INSERT excepttest VALUES (1, 3);
GO
INSERT excepttest SELECT excepttest.* FROM excepttest
GO 21
SELECT COUNT(*) FROM excepttest
GO
CREATE INDEX IX_Test ON excepttest (zestaw, [type]);
GO

DECLARE @zestaw int = 1;
SET STATISTICS IO ON
SET STATISTICS TIME ON
if NOT exists(select * from excepttest where zestaw<>@zestaw OR [type]<>3) 
   SELECT 'all match'
ELSE
   SELECT 'some match';

IF NOT EXISTS (
        SELECT zestaw, [type] FROm excepttest
        EXCEPT
        SELECT zestaw, [type] FROm excepttest where zestaw=@zestaw and [type]=3
        )
   SELECT 'all match'
ELSE
   SELECT 'some match';

SET STATISTICS IO OFF
SET STATISTICS TIME OFF
DROP TABLE excepttest