0

We know that when using NOT IN condition on a subquery which has NULL values the query will not produce any results, e.g.:

CREATE TABLE movie ( title TEXT ); INSERT INTO movie (title) VALUES (NULL); INSERT INTO movie (title) VALUES ('ABC'); SELECT * FROM movie WHERE 'Titanic' NOT IN (select title from movie)

The query above doesn't return any results.

However if the query is:

SELECT * FROM movie WHERE 'Titanic' NOT IN (title)

then the row with title=ABC is returned. Why the queries don't return the same results? Isn't NOT IN condition translated as WHERE 'Titanic' != 'ABC' AND 'Titanic' != NULL in both cases?

Link to db-fiddle

Improve this question
4
  • 2
    in both cases .. no. The last query just uses the title value from the current row, i.e. 'Titanic' NOT IN ('ABC') for that row. Commented May 10, 2024 at 7:57
  • @KekuSemau Why isn't the last query translated to 'Titanic' NOT IN ('ABC', NULL)? Commented May 10, 2024 at 11:08
  • 2
    It's a simple select that is executed row by row. There is no subquery or anything that would break out of the current row. NOT IN (title) here with one field is equivalent to != title. Commented May 10, 2024 at 11:16
  • 1
    WHERE 'Titanic' NOT IN (title) is just the same as WHERE 'Titanic' <> title. so for the 'ABC' row NULL doesn't get involved Commented May 10, 2024 at 12:35

1 Answer 1

Reset to default
0

NULL is not a value, it is the absence of a value. You cannot compare to NULL traditionally with equality operators like = or <>. The result of those comparisons to NULL will result in INDETERMINATE, not a boolean value.

The IN and NOT IN constructs use the equality operators under the hood. You are better off rewriting your query to use NOT EXISTS for instance, which does not care about NULL or non-NULL values, but cares about the existence of rows instead.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I know that NOT EXISTS is better to handle NULLs however my question is not about which approach is better but specifically why the seemingly identical queries return different results.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.