How can I make NOT IN clause NULL-safe?

Question

Here is my table structure:

// questions_and_answers
+----+-------------------+------+----------+
| id |       body        | type | related  |
+----+-------------------+------+----------+
| 1  | a question        | 0    | NULL     |
| 2  | my answer         | 1    | 1        |
| 3  | another answer    | 1    | 1        |
| 4  | another question  | 0    | NULL     |
| 5  | another answer    | 1    | 4        |
| 6  | another answer    | 1    | 1        |  
+----+-------------------+------+----------+
-- type column: it is either 0 for questions and 1 for answers.
-- related column: it is either null for questions and "the id of its question" for answers

Now I need to select all unanswered questions. Here is my query:

SELECT *
FROM questions_and_answers AS qa
WHERE
  type = 0 -- just questions
  AND
  qa.id NOT IN (SELECT q.related FROM qanda q WHERE q.type <> 0) -- unanswered ones

It works well and all fine.

What's my question? My query matches no row when there is a row like this:

| 7  | another answer    | 1    | NULL      |

See? the value of type is 1, so it is an answer. But the value of related is NULL, so it doesn't point to any question. In general that row makes no sense, but probably sometimes happens (when a question is removed and we set the related of its answers to null). In this case, the result of my query is "no row selected".

Why? And how can I make my query safe against such a situation? (safe == ignore them and still match unanswered questions)

Please leave a comment and explain what's wrong with my question? Why downvote? — Martin AJ, May 07 '18 at 11:49
Either switch to NOT EXISTS, or make sure the sub-query returns no null-values. — jarlh, May 07 '18 at 11:49
See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, May 07 '18 at 11:49
@jarlh `NOT EXISTS` throws: `Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'EXISTS (SELECT ...` — Martin AJ, May 07 '18 at 11:53
https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another — Paul Spiegel, May 07 '18 at 11:55

score 1 · Accepted Answer · answered May 07 '18 at 11:53

1

Use not exists:

where not exists (select 1 from quanda q where q.related = qa.id and q.type <> 0)

I strongly recommend that you never use not in with a subquery -- specifically because of the NULL issue. Just use not exists. It is also easier to optimize with an index.

answered May 07 '18 at 11:53

Gordon Linoff

1,242,037
58
646
786

Thank you for both the answer and the tip. Upvote. Just do you know why my question earned lots of downvotes? Just because of lack of a fiddle? – Martin AJ May 07 '18 at 11:55
@MartinAJ . . . I have no idea. I no longer downvote, and find non-commented downvotes especially rude. – Gordon Linoff May 08 '18 at 02:27

schlebe · Answer 2 · 2018-05-07T12:58:27.100

1

You must only remove all these records (related=NULL) from second SELECT like this

SELECT *
FROM qa
WHERE type = 0 -- just questions
  AND id NOT IN 
    (SELECT related 
       FROM qa
       WHERE type <> 0
         AND related IS NOT NULL
    )

edited May 07 '18 at 12:58

answered May 07 '18 at 12:04

schlebe

3,387
5
37
50

The second query is wrong. A static (non-correlated) NOT EXISTS subquery doesn't make sense here. – Paul Spiegel May 07 '18 at 12:12
yes it is true. I will remove this part from my answer. Thanks! – schlebe May 07 '18 at 12:57

How can I make NOT IN clause NULL-safe?

2 Answers2