MySQL SELECT x FROM a WHERE NOT IN ( SELECT x FROM b ) – Unexpected result

by

From documentation:

To comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

This is exactly your case.

Both IN and NOT IN return NULL which is not an acceptable condition for WHERE clause.

Rewrite your query as follows:

SELECT  *
FROM    match m
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    email e
        WHERE   e.id = m.id
        )

More Related Contents:

  1. Join vs. sub-query
  2. How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
  3. Natural Sort in MySQL
  4. Best way to test if a row exists in a MySQL table
  5. MySQL get row position in ORDER BY
  6. Subqueries vs joins
  7. MySQL Results as comma separated list
  8. MySQL unknown column in ON clause
  9. MySQL not using indexes with WHERE IN clause?
  10. SQL: Find the max record per group [duplicate]
  11. Group by month and year in MySQL
  12. calculate a sum of type time using sql
  13. MySQL: Transactions vs Locking Tables
  14. MySQL: What is a reverse version of LIKE?
  15. Remove duplicates using only a MySQL query?
  16. Group by date only on a Datetime column
  17. Finding the next available id in MySQL
  18. How exactly does using OR in a MySQL statement differ with/without parentheses?
  19. MySQL: How to copy rows, but change a few fields?
  20. How to renumber primary index
  21. Query that ignore the spaces
  22. mysql automatically store record creation timestamp
  23. Renaming foreign-key columns in MySQL
  24. update columns values with column of another table based on condition [duplicate]
  25. MySQL GROUP BY behavior
  26. How to get rid of “Error 1329: No data – zero rows fetched, selected, or processed”
  27. MySql bulk load command line tool
  28. ALTER TABLE without locking the table?
  29. MySQL: Typecasting NULL to 0
  30. How to get the byte size of resultset in an SQL query?

Leave a Comment