0

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

Erik
  • 75
  • 5
  • 1
    1. You don't need *distinct* - 2. Is `brand` nullable? Use *not exists*. – Stu Oct 20 '22 at 18:40
  • Yes, the brand column in media can be null. That is where the issue occurs: when there is media rows with media_type=7 but all of them have brand=null, then the subquery returns a single row with null – Erik Oct 20 '22 at 18:42
  • @Erik then replace the not in with not exists which is null-safe. – Stu Oct 20 '22 at 18:55

2 Answers2

1

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
);
Stu
  • 30,392
  • 6
  • 14
  • 33
  • thanks, this resolved the issue. I obviously don't fully understand the EXISTS criteria. But the query now produce what I want. – Erik Oct 20 '22 at 19:13
0

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

MC7836
  • 84
  • 8