Search code examples
sqlnullmariadbin-subquery

Using NOT IN( ... ) when subquery result a single NULL row

I get unexpected results for a NOT IN criteria, when the subquery returns a single NULL result row.

There's two tables, brands and media. The goal is to get a result only including the brands that does not have media of the given media_type associated with it.

SELECT * 
FROM brands 
WHERE id NOT IN (
    SELECT DISTINCT brand AS 'id'
    FROM media
    WHERE media_type=7
)

When there are entries of media_type=7 with brands associated, so the subquery returns a list of at least one valid id, the query works as expected.

However if no entries of media_type=7 are associated with any brand the subquery returns a single row with a NULL value. Then the total query returns an empty set instead of the expected: a result with all brands rows.

What's the error I'm doing here?

Using 10.4.26-MariaDB and tables are InnoDB types

Solution

  • Try the following correlated exists query

    select * 
from brands b
where not exists (
  select * from media m
    where m.media_type = 7 and m.brand = b.Id
);