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
