Sql Not IN Query using Join

Question

How can I write following query using left join here is my Sample Query

SELECT * 
FROM STUDENT st 
WHERE st.stdid not IN 
   (    SELECT csch.stid 
        FROM courseSchedule csch,    section sctn 
        WHERE cschno.sectno = sctn.sectno 
             AND cschno.college= sctn.college
             AND sctn.college = st.college AND sctn.year = 2013 and sctn.semester = 'spring' 
     )
     AND st.college = 1

Why don't you use EXISTS?? – turutosiya Dec 25 '14 at 11:56 — turutosiya, Dec 25 '14 at 11:56

StuartLC · Answer 1 · 2014-12-26T20:06:38.607

5

TO move the NOT IN subquery to a LEFT JOIN, do left join on the NOT IN key criteria, and instead, check for the Right table KEY as NULL. Note that you can also convert the WHERE join in the subquery to an ANSI join as well:

SELECT st.*
  FROM STUDENT st 
    LEFT JOIN courseSchedule csch 
      ON st.stdid = csch.stid 
    INNER JOIN section sctn 
      ON cschno.sectno = sctn.sectno 
     AND cschno.college= sctn.college
     AND sctn.college = st.college 
     AND sctn.year = 2013 
     AND sctn.semester = 'spring'
  WHERE st.college = 1
     AND csch.stid IS NULL; -- "Not IN"

edited Dec 26 '14 at 20:06

answered Dec 25 '14 at 10:07

StuartLC

104,537
17
209
285

i have little modified my query – Waqas Ali Dec 25 '14 at 10:10
2

You can handle additonal filter criteria in the same way. Note [there is a difference](http://stackoverflow.com/a/10297312/314291) between placing the criteria in the inner `LEFT JOIN` criteria, and in the outer `WHERE` clause which you need to be wary of. – StuartLC Dec 25 '14 at 10:12
can you guide me about that – Waqas Ali Dec 25 '14 at 18:33
I've added the additional criteria. Basically, unless ALL the criteria are met, the `RHS` table columns will be NULL because of the Left join, which is detected in the `WHERE` clause. – StuartLC Dec 26 '14 at 20:07

score 1 · Accepted Answer · answered Dec 29 '14 at 03:33

@Waqas, I would use EXISTS for this (actually NOT EXISTS) as a commenter suggested above:

SELECT * FROM STUDENT st
 WHERE st.college = 1
   AND NOT EXISTS ( SELECT 1 FROM courseSchedule csch, section sctn
                     WHERE csch.stid = st.stdid
                       AND csch.sectno = sctn.sectno
                       AND csch.college = sctn.college
                       AND sctn.year = 2013
                       AND sctn.semester = 'spring'
                       AND sctn.college = st.college )

I notice you also have a typo in your query (the alias for courseSchedule is csch, not cschno).

Hope this helps.

Sql Not IN Query using Join

2 Answers2