Display both null and not null under same id but not both null or not null like XOR logic

Question

Consider the following Employee table:

In this table I need to select only the ids which have both null and not null marks, and it should not be both not null or both are null in col1.

Answer 1

count(column) and count(*) will differ if NULL exists:

SELECT ID, Col1 
FROM #TestData
WHERE id IN 
 (
   SELECT id
   FROM #TestData
   GROUP BY id
   HAVING COUNT(*) > COUNT(Col1) -- at least one NULL 
      AND COUNT(Col1) > 0        -- at least one NOT NULL
 )

Most DBMSes support Analytical Functions, if the data is result of a more complex query this might be more efficient because it avoids accessing the same table twice:

SELECT ID, Col1
FROM 
 (
   SELECT ID, Col1,
      COUNT(Col1) OVER (PARTITION BY ID) AS cntNotNull,
      COUNT(*) OVER (PARTITION BY ID) AS cntNull
   FROM #TestData AS t
 ) AS dt
WHERE cntNull > cntNotNull
  AND cntNotNull > 0

Answer 2

select * from table 
where id in ( select id from table where col1 is     null 
              intersect 
              select id from table where col1 is not null 
            )

select t1.* from table t1
where exists ( select 1 from table where id = t1.id and col1 is     null )    
  and exists ( select 1 from table where id = t1.id and col1 is not null ) 

Answer 3

Here are two options, both based on using GROUP BY and HAVING. The syntax is for Microsoft SQL Server but should be easily adaptable to any other RDBMS syntax.

/* SETUP: Run the following once */
SET NOCOUNT ON;

CREATE TABLE #TestData (ID INT NOT NULL, Col1 INT NULL);
INSERT INTO #TestData (ID, Col1) VALUES (100, NULL);
INSERT INTO #TestData (ID, Col1) VALUES (100, 10);
INSERT INTO #TestData (ID, Col1) VALUES (101, NULL);
INSERT INTO #TestData (ID, Col1) VALUES (101, NULL);
INSERT INTO #TestData (ID, Col1) VALUES (102, 14);
INSERT INTO #TestData (ID, Col1) VALUES (102, 11);
INSERT INTO #TestData (ID, Col1) VALUES (103, NULL);
INSERT INTO #TestData (ID, Col1) VALUES (103, 12);
INSERT INTO #TestData (ID, Col1) VALUES (103, NULL);
/* END SETUP */


SELECT td.ID, td.Col1
FROM   #TestData td
INNER JOIN (
            SELECT tmp.ID, MAX(tmp.Col1) AS [Col1]
            FROM   #TestData tmp
            GROUP BY tmp.ID
            HAVING SUM(CASE WHEN tmp.Col1 IS NOT NULL THEN 1 ELSE 0 END) = 1
           ) ids
        ON ids.ID = td.ID;

-- This method appears to be better on SQL Server
SELECT td.ID, td.Col1
FROM   #TestData td
WHERE  td.ID IN (
            SELECT tmp.ID
            FROM   #TestData tmp
            GROUP BY tmp.ID
            HAVING SUM(CASE WHEN tmp.Col1 IS NOT NULL THEN 1 ELSE 0 END) = 1
           );

To be fair, and because I was curious about the INTERSECT approach proposed by @Paparazzi, I tested both of @Paparazzi's methods along with the two shown above. I also wrapped it and each of the two methods shown above in SET STATISTICS TIME, IO On and Off, and ended with a PRINT to more clearly separate the test output in the "Messages" tab.

SET STATISTICS TIME, IO ON;

SELECT td.ID, td.Col1
FROM   #TestData td
WHERE  td.ID IN (
                 SELECT ID FROM #TestData WHERE Col1 IS     NULL
                 INTERSECT
                 SELECT ID FROM #TestData WHERE Col1 IS NOT NULL
                );

SET STATISTICS TIME, IO OFF;
PRINT '----------------------------------';

SET STATISTICS TIME, IO ON;

SELECT td.ID, td.Col1
FROM   #TestData td
WHERE  EXISTS (SELECT 1 FROM #TestData WHERE ID = td.ID AND Col1 IS NULL)
  AND  EXISTS (SELECT 1 FROM #TestData WHERE ID = td.ID AND Col1 IS NOT NULL)

SET STATISTICS TIME, IO OFF;
PRINT '----------------------------------';

Results:

Which Test:                  Query Cost    Scan Count   Logical Reads
GROUP BY via INNER JOIN      54%            2            2
GROUP BY via IN list         11%           10           10
INTERSECT                    17%            3           17
EXISTS                       18%            3           17

Conclusion (thus far)

It is hard to say at the moment considering that the results are the same across all 4 methods tested above, but the logic is different and would produce slight different results if some test cases were added, such as an ID having 3 records and two of them were NOT NULL (for Col1) but different, or having 3 records and two of them were NOT NULL but they were the same value. Or what if there are cases with either a single record of NULL or a single NOT NULL record? Without additional input / clarification from the O.P. we can only assume that those other cases might never happen.

Also, while the IN list method has the lowest Query Cost, that is based on the Estimated Cost which is not necessarily a reliable indicator. The INNER JOIN method has the lowest number of scans and logical reads, but this is also a rather small data set to test with. Again, further clarification would help.

Answer 4

Select NullTest.ID, NullTest.Col1
    From NullTest 
    Join (Select ID From NullTest Where Col1 Is Not Null Group By ID) 
         NotNulls 
         On NotNulls.ID = NullTest.ID
    Join (Select ID From NullTest Where Col1 Is Null Group By ID) 
         Nulls 
         On Nulls.ID = NullTest.ID ;