sql server - SQL (TSQL) - Select values in a column where another column is not null? -

i keep simple- know if there way select values in column when never has null in column. example.

       b -----  -----    1      7   2      7 null     7   4      9   1      9   2      9

from above set want 9 b , not 7 because 7 has null in a. wrap subquery , use in clause etc. part of pretty unique set , looking keep efficient.

i should note purposes one-way comparison... returning values in b , examining a.

i imagine there easy way missing, being in thick of things don't see right now.

you can this:

select * t t.b not in (select b t null);

if want distinct b values, can do:

select b t group b having sum(case when null 1 else 0 end) = 0;

and, finally, use window functions:

select a, b (select t.*,              sum(case when null 1 else 0 end) on (partition b) nullcnt       t      ) t nullcnt = 0;

Scrio