Databases
StackOverflow2013
Postsdbo
POSQL Server: Why does comparison null=value return true for NOT IN?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server: Why does comparison null=value return true for NOT IN?
    primarykey
    data
    text
    <p>Why does the comparison of <code>value</code> to <code>null</code> return false, except when using a <code>NOT IN</code>, where it returns true?</p> <hr> <p>Given a query to find all stackoverflow users who <strong>have</strong> a post:</p> <pre><code>SELECT * FROM Users WHERE UserID IN (SELECT UserID FROM Posts) </code></pre> <p>This works as expected; i get a list of all users who have a post.</p> <p>Now query for the inverse; find all stackoverflow users who <strong>don't</strong> have a post:</p> <pre><code>SELECT * FROM Users WHERE UserID NOT IN (SELECT UserID FROM Posts) </code></pre> <p>This returns no records, which is incorrect.</p> <p>Given hypothetical data<sup>1</sup></p> <pre><code>Users Posts ================ =============================== UserID Username PostID UserID Subject ------ -------- ------- ------ ---------------- 1 atkins 1 1 Welcome to stack ov... 2 joels 2 2 Welcome all! ... ... ... ... 399573 gt6989b ... ... ... ... ... ... 10592 null (deleted by nsl&amp;fbi... ... ... </code></pre> <p>And assume the rules of NULLs:</p> <ul> <li><code>NULL = NULL</code> evaluates to unknown</li> <li><code>NULL &lt;&gt; NULL</code> evaluates to unknown</li> <li><code>value = NULL</code> evaluates unknown</li> </ul> <p>If we look at the 2nd query, we're interested in finding all rows where the Users.UserID is <strong>not</strong> found in the Posts.UserID column. i would proceed logically as follows:</p> <p>Check UserID 1</p> <ul> <li><code>1 = 1</code> returns true. So we conclude that this user has some posts, and do not include them in the output list</li> </ul> <p>Now check UserID 2:</p> <ul> <li><code>2 = 1</code> returns false, so we keep looking</li> <li><code>2 = 2</code> returns true, so we conclude that this user has some posts, and do not include them in the output list</li> </ul> <p>Now check UserID 399573 </p> <ul> <li><code>399573 = 1</code> returns false, so we keep looking</li> <li><code>399573 = 2</code> returns false, so we keep looking</li> <li>...</li> <li><code>399573 = null</code> returns unknown, so we keep looking</li> <li>...</li> </ul> <p>We found no posts by UserID 399573, so we would include him in the output list.</p> <p>Except SQL Server doesn't do this. If you have a NULL in your <code>in</code> list, then suddenly it finds a match. <em>It suddenly finds a match.</em> Suddenly <code>399573 = null</code> evaluates to true.</p> <p>Why does the comparison of <code>value</code> to <code>null</code> return unknown, except when it returns true?</p> <p><strong>Edit</strong>: i know that i can workaround this <strike>nonsensical</strike> behavior by specifically excluding the nulls:</p> <pre><code>SELECT * FROM Users WHERE UserID NOT IN ( SELECT UserID FROM Posts WHERE UserID IS NOT NULL) </code></pre> <p>But i shouldn't have to, as far as i can tell the boolean logic should be fine without it - hence my question.</p> <h2>Footnotes</h2> <ul> <li><sup>1</sup> hypothetical data; if you don't like it: make up your down.</li> <li>celko now has his own tag</li> </ul>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USIan Boyd
    1. USIan Boyd
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server: Why does comparison null=value return true for NOT IN?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Server: Why does comparison null=value return true for NOT IN?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Server: Why does comparison null=value return true for NOT IN?
      1. USnet_prog
      1. VTFavorite
    1. CODuplicate: See http://stackoverflow.com/questions/129077/sql-not-in-constraint-and-null-values
      singulars
      1. POSQL Server: Why does comparison null=value return true for NOT IN?
      1. USKeith
    2. COi see. It's not pointed out in the other question - but it's a bug in how the `NOT IN` is expanded. Rather than logically determining if the row is *"not in"* the list, it is expanding it to a series of `and <>` clauses.
      singulars
      1. POSQL Server: Why does comparison null=value return true for NOT IN?
      1. USIan Boyd
    3. CO@Ian Boyd = I don't think that's a bug. Something else to remember is `IN` uses `or` and `NOT IN` uses `AND`. When you are evaluating against a `NULL` with an inequality vs. a known value, you will always get a false since there is no way to know if it matches or not.
      singulars
      1. POSQL Server: Why does comparison null=value return true for NOT IN?
      1. USJNK
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload