Convert NOT IN NOT EXISTS

Question

Having a nightmare of time understanding using NOT EXISTS is, first of all, how to transform my NOT IN solution below so that I can understand how I achieved the results. You have several askTom articles, oracle and stackoverflow forums, but they cannot find anything to clearly understand this problem. My apologies if I missed this through a search for nubia.

SELECT s.S_Fname, s.S_Lname FROM STUDENT s WHERE s.S_Sex = 'F' AND S.S_Id NOT IN(SELECT e.S_Id FROM ENROLLMENT e WHERE e.Mark < 70); 

A little help with the content, trying to find women students who never got marks below 70 in any class in which they were enrolled.

Answer 1

This is pretty simple when you hang it:

 SELECT s.S_Fname, s.S_Lname FROM STUDENT s WHERE s.S_Sex = 'F' AND S.S_Id NOT IN(SELECT e.S_Id -- take this line FROM ENROLLMENT e WHERE e.Mark < 70); 

This line basically compares S.S_Id with all e.S_Id values ​​that come from the subquery.

Now change this to NOT EXISTS and put the equality check S.S_Id = e.S_Id inside the subquery:

 SELECT s.S_Fname, s.S_Lname FROM STUDENT s WHERE s.S_Sex = 'F' AND NOT EXISTS (SELECT e.S_Id FROM ENROLLMENT e WHERE (e.Mark < 70) -- if this is complex, you'll need parentheses AND S.S_Id = e.S_Id); 

A slight possible change is to realize that (SELECT e.S_Id ... really doesn’t need e.S_Id . Subqueries with EXISTS and NOT EXISTS just check to see if there are rows returned or not and the column values ​​do not matter. You can put SELECT * or a constant (usually SELECT 1 ) or SELECT NULL or even SELECT 1/0 (Yes, it will work!):

 SELECT s.S_Fname, s.S_Lname FROM STUDENT s WHERE s.S_Sex = 'F' AND NOT EXISTS (SELECT 1 FROM ENROLLMENT e WHERE e.Mark < 70 AND S.S_Id = e.S_Id); 

Another important consideration is that when converting in this way, the (seemingly equivalent) NOT EXISTS and NOT IN query records are really equivalent only if both S_Id columns S_Id not NULL. If the column e.S_Id is NULL, NOT IN can cause the entire query to return no rows at all (since x NOT IN (a, b, c, ...) equivalent to x<>a AND x<>b AND ... , and this condition cannot be true if one of a,b,c... NULL .)

For similar reasons, you will have different results if S.S_Id is NULL (which is unlikely in this case, since it is probably the primary key, but in other cases it matters.)

So it is almost always better to use NOT EXISTS , since it behaves differently even if any column is NULL (checking S.S_Id = e.S_Id will discard rows with zero earlier), and usually this behavior is necessary. There are many details in the question: NOT IN vs NOT EXISTS , in the answer by @Martin Smith. You will also find ways to convert NOT IN to NOT EXISTS and support zero (unpleasant) behavior.

Answer 2

Not quite what you requested, but here you can do it without NOT EXISTS - create “achievements below 70” as a view, LEFT OUTER JOIN and check for null values ​​like this ...

 SELECT s.S_Fname, s.S_Lname FROM STUDENT s LEFT JOIN ( SELECT S_Id FROM ENROLLMENT WHERE Mark < 70 ) e ON e.S_Id = s.S_Id WHERE e.S_Id IS NULL AND s.S_Sex = 'F'; 

Perhaps this will help for your general understanding of SQL. There are pros and cons for both approaches.

Click here to go to SQL Fiddle