SQL IN () is including NULL values

Question

Employee table:

+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
| emp_id | fname    | lname     | start_date | end_date | superior_emp_id | dept_id | title              | assigned_branch_id |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+
|      1 | Michael  | Smith     | 2005-06-22 | NULL     |            NULL |       3 | President          |                  1 |
|      2 | Susan    | Barker    | 2006-09-12 | NULL     |               1 |       3 | Vice President     |                  1 |
|      3 | Robert   | Tyler     | 2005-02-09 | NULL     |               1 |       3 | Treasurer          |                  1 |
|      4 | Susan    | Hawthorne | 2006-04-24 | NULL     |               3 |       1 | Operations Manager |                  1 |
|      5 | John     | Gooding   | 2007-11-14 | NULL     |               4 |       2 | Loan Manager       |                  1 |
|      6 | Helen    | Fleming   | 2008-03-17 | NULL     |               4 |       1 | Head Teller        |                  1 |
|      7 | Chris    | Tucker    | 2008-09-15 | NULL     |               6 |       1 | Teller             |                  1 |
|      8 | Sarah    | Parker    | 2006-12-02 | NULL     |               6 |       1 | Teller             |                  1 |
|      9 | Jane     | Grossman  | 2006-05-03 | NULL     |               6 |       1 | Teller             |                  1 |
|     10 | Paula    | Roberts   | 2006-07-27 | NULL     |               4 |       1 | Head Teller        |                  2 |
|     11 | Thomas   | Ziegler   | 2004-10-23 | NULL     |              10 |       1 | Teller             |                  2 |
|     12 | Samantha | Jameson   | 2007-01-08 | NULL     |              10 |       1 | Teller             |                  2 |
|     13 | John     | Blake     | 2004-05-11 | NULL     |               4 |       1 | Head Teller        |                  3 |
|     14 | Cindy    | Mason     | 2006-08-09 | NULL     |              13 |       1 | Teller             |                  3 |
|     15 | Frank    | Portman   | 2007-04-01 | NULL     |              13 |       1 | Teller             |                  3 |
|     16 | Theresa  | Markham   | 2005-03-15 | NULL     |               4 |       1 | Head Teller        |                  4 |
|     17 | Beth     | Fowler    | 2006-06-29 | NULL     |              16 |       1 | Teller             |                  4 |
|     18 | Rick     | Tulman    | 2006-12-12 | NULL     |              16 |       1 | Teller             |                  4 |
+--------+----------+-----------+------------+----------+-----------------+---------+--------------------+--------------------+

Query:

SELECT emp_id, fname, lname, title
FROM employee
WHERE emp_id IN (SELECT superior_emp_id FROM employee);

Query Result:

+--------+---------+-----------+--------------------+
| emp_id | fname   | lname     | title              |
+--------+---------+-----------+--------------------+
|      1 | Michael | Smith     | President          |
|      3 | Robert  | Tyler     | Treasurer          |
|      4 | Susan   | Hawthorne | Operations Manager |
|      6 | Helen   | Fleming   | Head Teller        |
|     10 | Paula   | Roberts   | Head Teller        |
|     13 | John    | Blake     | Head Teller        |
|     16 | Theresa | Markham   | Head Teller        |
+--------+---------+-----------+--------------------+

Subquery result:

+-----------------+
| superior_emp_id |
+-----------------+
|            NULL |
|               1 |
|               1 |
|               3 |
|               4 |
|               4 |
|               4 |
|               4 |
|               4 |
|               6 |
|               6 |
|               6 |
|              10 |
|              10 |
|              13 |
|              13 |
|              16 |
|              16 |
+-----------------+

If the subquery SELECT superior_emp_id FROM employee returns NULL for Michael Smith how is it that the IN() operator returns it in the final result set? I thought nothing was equal to null.

run "SELECT superior_emp_id FROM employee" query and you'll see that that result set will contain (1,1,3,4,4... etc). — algor, Feb 02 '16 at 16:21
@algor is right. You should have told us what you _expected_ to see. At present, your assumptions about what that subquery would do seem to be mistaken. — underscore_d, Feb 02 '16 at 16:56

Arth · Accepted Answer · 2016-02-02T17:06:35.663

If the subquery SELECT superior_emp_id FROM employee returns NULL for Michael Smith how is it that the IN() operator returns it in the final result set?

Short answer, it doesn't.

The subquery effectively returns the whole set of superior_emp_ids [NULL, 1, 1, 3, 3, 4, 4, 6, 6, 6, 4, 10, 10, 4, 13, 13, 4, 16, 16] for each row.

Your WHERE clause tests each emp_id to see if it is IN this set. And IN is basically a series of equals comparisons OR'd together.

Michael's emp_id is 1 and his row is returned because 1 = NULL OR 1 = 1 .... which can be written as FALSE OR TRUE .... returns TRUE.

You are correct in assuming that NULL doesn't equal anything, including NULL, so WHERE NULL IN (NULL, 1, FALSE, ... anything you like ...) will return FALSE. But that is not what's happening in your example.

N.B. To avoid any confusion it is much better to avoid NULL records on either side of an IN clause where possible as referenced by @Donal

I figured it out and what you stated in your answer was exactly how I figured it out. Took some thinking thought. Thanks! — Robert, Feb 02 '16 at 17:04

score 1 · Answer 2 · answered Feb 02 '16 at 16:23

1

SELECT superior_emp_id FROM employee

returns [NULL, 1, 3, 4, 6, 10, 13, 16]. I do not see the problem here.

answered Feb 02 '16 at 16:23

ZhukovRA

506
1
3
17

Then look closer! `IN` uses the same ANSI rules for comparing to `NULL` as `=` does: http://stackoverflow.com/questions/6362112/in-clause-with-null-or-is-null#comment7448343_6362112 MySQL does not allow non-ANSI comparisons, and `in` effectively just performs iterative comparison to each element in the list, and so a `null` is never `in` a list, even if that list contains a `null`. – underscore_d Feb 02 '16 at 16:48
@underscore_d True, but `1` (Michael's emp_id) is in `[NULL, 1, 3, 4, 6, 10, 13, 16]` – Arth Feb 02 '16 at 16:51
...but OP is comparing against Michael's `superior_emp_id`, which _is_ `null`. – underscore_d Feb 02 '16 at 16:52
@underscore_d `WHERE emp_id IN (SELECT superior_emp_id FROM employee)` .. straight from the question – Arth Feb 02 '16 at 16:52
1

Yeah, I misread this. I think all the answers about `null`s, while educational, are red herrings, and that the OP simply hasn't understood what the sub `select` was going to do. – underscore_d Feb 02 '16 at 16:53

Donal · Answer 3 · 2016-02-02T16:36:59.533

1

Have a look at the ANSI_NULLS setting in SQL Server.

Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL.

Taken from here.

If you don't want the NULL value you will need to add a WHERE clause to the sub query. For example:

SELECT emp_id, fname, lname, title
FROM employee
WHERE emp_id IN (SELECT superior_emp_id FROM employee WHERE superior_emp_id IS NOT NULL);

edited Feb 02 '16 at 16:36

answered Feb 02 '16 at 16:26

Donal

31,121
10
63
72

Thanks for the tip. But my question was why is it that the `IN` operator was successfully comparing with NULL since nothing can be compared to NULL except for the IS NOT NULL and IS NULL operators. – Robert Feb 02 '16 at 16:29
1

@RobertRocha ok, sorry I thought the question was for SQL Server. With MySQL - it looks like you cannot turn off ANSI_NULLS. See here: http://stackoverflow.com/questions/2747279/something-about-ansi-nulls – Donal Feb 02 '16 at 16:41
1

@RobertRocha In any event, you should avoid the use of NULL within IN clauses - see here: https://boundlessjourney.wordpress.com/2013/05/11/behavior-of-null-values-in-not-in-clause-mysql/ – Donal Feb 02 '16 at 16:47

SQL IN () is including NULL values

3 Answers3