9

I want to get only rows having a value NULL and some other value than NULL for a particular username column.

If both rows have null for that particular username or both have some values other than null then it should not appear in output. If there are more than two rows for same username with null and some other value then they should appear.

Below is example sample and output. How it can be done using sql query?

+----------+-------+
| username | col2  |
+----------+-------+
| a        | abc   |
| a        | ef    |
| b        | null  |
| b        | null  |
| c        | der   |
| c        | null  |
+----------+-------+

output

+----------+------+
| username | col2 |
+----------+------+
| c        | der  |
| c        | null |
+----------+------+
4
  • 1
    What if there are 2 rows with d, der and 2 with d, null? Dec 5, 2014 at 12:05
  • 1
    @ypercube Then all the 4 rows of d should appear Dec 5, 2014 at 12:24
  • 1
    If there are rows with e, one, e, two and 2 or more with e, null? Dec 5, 2014 at 12:43
  • 1
    @ypercube then all rows should appear . Dec 8, 2014 at 8:14

6 Answers 6

12

You should be able to use conditional aggregation to get the username with both a value in col2 as well as null.

I'd suggest using a HAVING clause with the conditions. The query would be similar to:

select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
  and sum(case when col2 is null then 1 else 0 end) = 1

See SQL Fiddle with Demo. This query groups your data by each username and then uses conditional logic to check if col2 meets both conditions you want - where col2 is not null and col2 is null.

You can then use this in a subquery, etc to get the username and col2 values:

select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) = 1
    and sum(case when col2 is null then 1 else 0 end) = 1
) d
  on t.username = d.username

See SQL Fiddle with Demo.

If you have more than one col2 row with both null and another value, then you just need to alter the HAVING clause slightly:

select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) >= 1
    and sum(case when col2 is null then 1 else 0 end) >= 1
) d
  on t.username = d.username;

See SQL Fiddle with Demo

3
  • Your query missed one point(actually I have not clearly mentioned in question too). If there are more than two rows for same username with null and some other value then they should appear. in your query they wont come(for example in that fiddle if there is another row with user name 'c' and a null or some value. Dec 5, 2014 at 6:42
  • 1
    @ITresearcher That is a simple fix - you need to change the HAVING clause to be >=1 - sqlfiddle.com/#!3/8af72/2
    – Taryn
    Dec 5, 2014 at 11:07
  • Ok.thats correct. Answer by JGA also works. Dec 5, 2014 at 12:05
8

Another solution:

SELECT Y1.*
FROM dbo.yourtable AS Y1
WHERE Y1.username = ANY
(
    SELECT Y2.username 
    FROM dbo.yourtable AS Y2
    WHERE Y2.col2 IS NULL
    INTERSECT
    SELECT Y3.username 
    FROM dbo.yourtable AS Y3
    WHERE Y3.col2 IS NOT NULL
);

Execution Plan

In a similar logical vein:

SELECT Y.* 
FROM dbo.yourtable AS Y
WHERE EXISTS
    (
    SELECT * 
    FROM dbo.yourtable AS Y2 
    WHERE Y2.username = Y.username 
    AND Y2.col2 IS NULL
    )
AND EXISTS
    (
    SELECT * 
    FROM dbo.yourtable AS Y3 
    WHERE Y3.username = Y.username 
    AND Y3.col2 IS NOT NULL
    );

Execution plan

Yet another:

SELECT
    SQ1.username,
    SQ1.col2
FROM 
(
    SELECT
        Y.username, 
        Y.col2,
        MinCol2 = 
            MIN(CASE WHEN Y.col2 IS NULL THEN -1 ELSE 1 END) 
            OVER (PARTITION BY Y.username), 
        MaxCol2 = 
            MAX(CASE WHEN Y.col2 IS NULL THEN -1 ELSE 1 END) 
            OVER (PARTITION BY Y.username)
    FROM dbo.yourtable AS Y
) AS SQ1
WHERE 
    SQ1.MinCol2 = -SQ1.MaxCol2;

Execution Plan

1
  • Nice answer. Even it has better performance bcoz my table was huge. Dec 6, 2014 at 4:40
5

Just another way to do it:

; WITH cte AS
  ( SELECT username, col2,
           cnt_all  = COUNT(*) OVER (PARTITION BY username),
           not_null = COUNT(col2) OVER (PARTITION BY username)
    FROM yourtable AS a
  )
SELECT username, col2
FROM cte
WHERE cnt_all > not_null 
  AND not_null > 0 ;
4

This one works too. SQL Fiddle demo

I obtain C1 as the total rows for each username, C2 as the total null rows for each user name and I compare these values later.

SELECT username, col2 FROM
(
SELECT *,
(SELECT Count(*) FROM T Where username = T1.username) C1,
(SELECT Count(*) FROM T Where username = T1.username and col2 is null) C2
FROM T T1
) T2
WHERE C2 > 0 And C1 <> C2
3

I would use the sub-query to select those usernames like:

select username
from   dbo.yourtable
group by username
having sum(distinct case when col2 is not null then 1 else 2 end) = 3;
0
-1

I tried with this one...

select a.username from  
(select username ,col2 
   from yourtable
where col2 is null) a,(select username ,col2 
                       from yourtable
                        where col2 is not null) b
where a.username=b.username;
1
  • 2
    This will induce a cross join. If for a username, there are 3 rows with null col2 and 2 rows with non-null col2, the end result will have 6 rows, not 5. And col2 will not be in the output. Dec 5, 2014 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.