8

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 |
+----------+------+
  • What if there are 2 rows with d, der and 2 with d, null? – ypercubeᵀᴹ Dec 5 '14 at 12:05
  • @ypercube Then all the 4 rows of d should appear – IT researcher Dec 5 '14 at 12:24
  • If there are rows with e, one, e, two and 2 or more with e, null? – ypercubeᵀᴹ Dec 5 '14 at 12:43
  • @ypercube then all rows should appear . – IT researcher Dec 8 '14 at 8:14
11

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

  • 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. – IT researcher Dec 5 '14 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 '14 at 11:07
  • Ok.thats correct. Answer by JGA also works. – IT researcher Dec 5 '14 at 12:05
7

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

  • Nice answer. Even it has better performance bcoz my table was huge. – IT researcher Dec 6 '14 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;
-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;
  • 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. – ypercubeᵀᴹ Dec 5 '14 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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