Search code examples
mysqlsqlsql-serverisnull

SQL Server all(select ...) is null

I search but i cannot find anything about it. I need SQL server query like 

select t1.x 
 from @tablename as t1 
 where all
   (select t2.y from @tablename as t2 where t1.x=t2.x) is null

@tablename's are same

But I can't use all(select ...) is null part of query.

Thanks.

Solution

  • Do you want not exists?

    select t1.x 
from @tablename as t1 
where not exists (select t2.y from @tablename as t2 where t1.x = t2.x)

    This tests that there are no matching values.

    Or, perhaps,

    select t1.x 
from @tablename t1 
where not exists (select 1
                  from @tablename as t2
                  where t1.x = t2.x and t2.y is not null
                 ) ;

    This tests that any matching value has NULL for y.