CODEWITHSUNDEEP
sqlsql-servert-sql
Nick Birke
Nick Birke

Reputation: 137

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

I will keep this simple- I would like to know if there is a good way to select all the values in a column when it never has a null in another column. For example.

  A      B
-----  -----

  1      7
  2      7
NULL     7
  4      9
  1      9
  2      9

From the above set I would just want 9 from B and not 7 because 7 has a NULL in A. Obviously I could wrap this as a subquery and USE the IN clause etc. but this is already part of a pretty unique set and am looking to keep this efficient.

I should note that for my purposes this would only be a one-way comparison... I would only be returning values in B and examining A.

I imagine there is an easy way to do this that I am missing, but being in the thick of things I don't see it right now.

Upvotes: 0

Views: 3212

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can do something like this:

select *
from t
where t.b not in (select b from t where a is null);

If you want only distinct b values, then you can do:

select b
from t
group by b
having sum(case when a is null then 1 else 0 end) = 0;

And, finally, you could use window functions:

select a, b
from (select t.*,
             sum(case when a is null then 1 else 0 end) over (partition by b) as NullCnt
      from t
     ) t
where NullCnt = 0;

Upvotes: 1

John Woo
John Woo

Reputation: 263703

The query below will only output one column in the final result. The records are grouped by column B and test if the record is null or not. When the record is null, the value for the group will increment each time by 1. The HAVING clause filters only the group which has a value of 0.

SELECT  B
FROM    TableName
GROUP   BY B
HAVING  SUM(CASE WHEN A IS NULL THEN 1 ELSE 0 END) = 0

If you want to get all the rows from the records, you can use join.

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  B
            FROM    TableName
            GROUP   BY B
            HAVING  SUM(CASE WHEN A IS NULL THEN 1 ELSE 0 END) = 0
        ) b ON  a.b = b.b

Upvotes: 1

Related Questions