MySQL “NOT IN” query 3 tables

by

Avoid NOT IN like the plague if

SELECT ID_Courses FROM Evaluation where `NAME`='JOHN' and Year=1

could ever contain NULL. Instead, use NOT EXISTS or Left Joins

use explicit joins, not 1980’s style joins using the WHERE clause

To illustrate the misery of NOT IN:

SQL NOT IN () danger

create table mStatus
(   id int auto_increment primary key,
    status varchar(10) not null
);
insert mStatus (status) values ('single'),('married'),('divorced'),('widow');

create table people
(   id int auto_increment primary key,
    fullName varchar(100) not null,
    status varchar(10)  null
);

Chunk1:

truncate table people;
insert people (fullName,`status`) values ('John Henry','single');
select * from mstatus where `status` not in (select status from people);

** 3 rows, as expected **

Chunk2:

truncate table people;
insert people (fullName,`status`) values ('John Henry','single'),('Kim Billings',null);
select * from mstatus where status not in (select status from people);

no rows, huh?

Obviously this is ‘incorrect’. It arises from SQL’s use of three-valued logic,
driven by the existence of NULL, a non-value indicating missing (or UNKNOWN) information.
With NOT IN, Chunk2 it is translated like this:

status NOT IN ('married', 'divorced', 'widowed', NULL)

This is equivalent to:

NOT(status="single" OR status="married" OR status="widowed" OR status=NULL)

The expression “status=NULL” evaluates to UNKNOWN and, according to the rules of three-valued logic,
NOT UNKNOWN also evaluates to UNKNOWN. As a result, all rows are filtered out and the query returns an empty set.

Possible solutions include:

select s.status
from mstatus s
left join people p
on p.status=s.status
where p.status is null

or use not exists

More Related Contents:

  1. Query database then email posts from where user is subbed to
  2. Using PHP data in MySQL [closed]
  3. Save the data into a database and return another value from database using PHP
  4. Checking the availability of rooms
  5. Can I parameterize the table name in a prepared statement?
  6. How to properly set up a PDO connection
  7. Getting raw SQL query string from PDO prepared statements
  8. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  9. MySQL Prepared statements with a variable size variable list
  10. Loading .sql files from within PHP
  11. SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens on line 102 [closed]
  12. Comma separated values in MySQL “IN” clause
  13. SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax — PHP — PDO [duplicate]
  14. Doing a while / loop to get 10 random results
  15. PHP – Import CSV file to mysql database Using LOAD DATA INFILE
  16. MYSQL Select from tables based on multiple rows
  17. Simple PHP SQL login troubleshooting
  18. Change date format (in DB or output) to dd/mm/yyyy – PHP MySQL
  19. How do you do multiple SQL statements in one mysql_query?
  20. MySql PHP select count of distinct values from comma separated data (tags)
  21. Laravel Eloquent get results grouped by days
  22. MySQL/SQL retrieve first 40 characters of a text field?
  23. How to check if a value already exists to avoid duplicates?
  24. how do I get month from date in mysql
  25. php/mysql zip code proximity search
  26. Updating column so that it contains the row position
  27. MYSQL select a piece of a string and order by that piece
  28. Aggregated query without GROUP BY
  29. PHP/MySQL Pagination
  30. Limit pagination page number

Leave a Comment