Select records from table where a given column value has no non-null values in another column

73
February 28, 2019, at 3:50 PM

In this simple table example:

ID    SUBID
1000  NULL
1000  NULL
1000  1
1000  NULL
1001  NULL
1001  NULL

I would like my query to return an ID of 1001 only, because all 1001 IDs have NULL in SUBID. 1000 should be excluded, because at least one 1000 ID also has a non-NULL in SUBID.

So something like (convert my plain English to SQL):

select distinct id from table where all records with that id have NULL in subid

Answer 1

Use a GROUP BY query and check that all SUBID entries are NULL using the BIT_AND() aggregate function:

select ID
from myTable
group by ID
having bit_and(SUBID is null)

Demo: https://www.db-fiddle.com/f/8dnfHV6VVVu7dvoZarTjdp/0

You can also replace the HAVING clause by

having count(SUBID) = 0

since COUNT() will ignore all NULL entries.

Demo: https://www.db-fiddle.com/f/t3FrL7zUAwGqqWDS4dQUg9/0

This version should work for any major RDBMS.

Or

having max(SUBID) is null

This works with most aggregate functions, since they will return NULL, if all entries are NULL. (COUNT() is an exception.)

However - MAX() or MIN() might be the fastest, if you have an index on (ID, SUBID).

Answer 2

You could use a NOT IN the subid where is null

select distinct id 
from table 
where id NOT IN (
  select distinct id from table where subid is null  
) 
Answer 3

You could use a NOT EXISTS clause to check for ID values which have a non-NULL subid value and exclude them from your result:

SELECT DISTINCT m1.ID
FROM myTable m1
WHERE NOT EXISTS (SELECT * 
                  FROM myTable m2 
                  WHERE m2.ID = m1.ID AND m2.subID IS NOT NULL)

Alternatively, you could count the rows associated with the ID value and also count the number of NULL subid values associated with that ID, and see if they are the same:

SELECT ID
FROM myTable m1
GROUP BY ID
HAVING COUNT(*) = SUM(subid IS NULL)

Output:

1001

Demo on dbfiddle

READ ALSO
#1062 - Duplicate entry '19' for key 'PRIMARY'

#1062 - Duplicate entry '19' for key 'PRIMARY'

I have the following table in MySQL version 55

92
google big query

google big query

Suppose there is a database with two tables namely Loans and Billpayment

118
calling mysql file from batch

calling mysql file from batch

I have a batch file which contain the following code:

96