CODEWITHSUNDEEP
sqloracle-databasesubquery
Ris
Ris

Reputation: 1912

NOT IN sub-query returning no results

I am trying to get a list of instructors who have never taught a course section.

I first select and I get bunch of results who taught a course 

SELECT w.COURSE_NO,z.SALUTATION, z.FIRST_NAME, z.LAST_NAME,z.ZIP
FROM INSTRUCTOR z , SECTION w
WHERE z.INSTRUCTOR_ID = w.INSTRUCTOR_ID;

but when I do NOT IN so that I can see what instructor never taught a course section it gives me no rows. 

SELECT a.SALUTATION, a.FIRST_NAME, a.LAST_NAME,a.ZIP
FROM INSTRUCTOR a,SECTION b 
WHERE a.INSTRUCTOR_ID = b.INSTRUCTOR_ID AND b.COURSE_NO NOT IN
(SELECT w.COURSE_NO
FROM INSTRUCTOR z , SECTION w
WHERE z.INSTRUCTOR_ID = w.INSTRUCTOR_ID)
ORDER BY a.SALUTATION, a.FIRST_NAME, a.LAST_NAME,a.ZIP;

why is it giving me no rows when I am trying to select those who never a course section ?

Upvotes: 0

Views: 565

Answers (1)

D Stanley
D Stanley

Reputation: 152521

Because you are getting only rows that are in SECTION. It seems like you want:

SELECT a.SALUTATION, a.FIRST_NAME, a.LAST_NAME,a.ZIP
FROM INSTRUCTOR a 
WHERE a.INSTRUCTOR_ID NOT IN
(SELECT w.INSTRUCTOR_ID 
FROM SECTION w)
ORDER BY a.SALUTATION, a.FIRST_NAME, a.LAST_NAME,a.ZIP;

Or

SELECT a.SALUTATION, a.FIRST_NAME, a.LAST_NAME,a.ZIP
FROM INSTRUCTOR a 
LEFT JOIN SECTION w ON a.INSTRUCTOR_ID = w.INSTRUCTOR_ID
WHERE w.INSTRUCTOR_ID IS NULL
ORDER BY a.SALUTATION, a.FIRST_NAME, a.LAST_NAME,a.ZIP;

Upvotes: 2

Related Questions