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

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

CodePudding user response：

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
);

CodePudding user response：

Hard to know the error without more details, but another solution could be to do a LEFT JOIN and then exclude the rows that join...

  SELECT
    b.*
  FROM
    brands b
    LEFT JOIN
    media m
    ON m.brand = b.id
    AND m.media_type = 7
  WHERE
    m.id IS NULL

ie. you select all the records where the join failed... since those were records that had a matching id and media_type of 7