Confusion over multiple row subquery comparison

Question

I'm working in Oracle SQL Developer right now and am stumped on a query. The prompt reads:
Provide an alphabetic listing of instructors who have never taught a course section. List the salutation, first name, last name and zip code.

This is my current attempt:

SELECT i.Salutation, i.First_Name, i.Last_Name, i.Zip
FROM Instructor i
  JOIN Section s 
    ON i.Instructor_Id = s.Instructor_Id
WHERE 0 in (SELECT COUNT(Instructor_Id)
FROM Section 
GROUP BY Instructor_Id)

My code works (returns desired output for the instructors) when I search for instructors who actually have classes, but it doesn't recognize when I search for those with none. I'm not too sure how to workaround this, any help is appreciated.

score 1 · Answer 1 · answered Mar 01 '18 at 22:21

1

The default join is INNER, which means only rows from section where the instructor id matches are returned. You probably want to specify LEFT OUTER JOIN [1]. The other common construct for this is to use WHERE NOT EXISTS [2]

[1] What is the difference between "INNER JOIN" and "OUTER JOIN"?

[2] NOT IN vs NOT EXISTS

answered Mar 01 '18 at 22:21

Ian McGowan

3,461
3
18
23

Gotcha. I need to make sure I'm familiar with how the joins work, thanks a ton. The Not Exists fixed this particular issue. – stasis100t Mar 02 '18 at 00:12
Yeah, you're gonna need to get friendly with INNER and LEFT OUTER joins at a minimum. And get friendly with the concept of NULL and the surprising fact that NULL != NULL is false as well as NULL = NULL is false too ;-) – Ian McGowan Mar 02 '18 at 00:39

score 1 · Accepted Answer · answered Mar 01 '18 at 22:21

1

You can use NOT EXISTS to get instructors that aren't in the Section table.

SELECT 
  Salutation,
  First_Name,
  Last_Name,
  Zip
FROM Instructor i
WHERE NOT EXISTS (
 SELECT
  *
 FROM Section s
 WHERE i.Instructor_Id = s.Instructor_Id)
ORDER BY Last_Name

answered Mar 01 '18 at 22:21

B.Porter

367
1
14

Ahh, I had no clue about this clause. This makes sense and worked great, thank you. – stasis100t Mar 02 '18 at 00:11

score 1 · Answer 3 · answered Mar 01 '18 at 22:59

The following finds instructors who have never taught any courses. Change the 'HAVING' clause condition to > 0 to get instructors who have taught some courses:

SELECT 
  i.Salutation,
  i.First_Name,
  i.Last_Name,
  i.Zip 
FROM Instructor i
left join section s
on i.instructor_id = s.instructor_id
group by 
i.instructor_id,
i.Salutation,
  i.First_Name,
  i.Last_Name,
  i.Zip  
having count(s.section_id)  = 0 /* > 0 if looking for instructors who have taught some courses */
order by i.last_name, i.first_name

score 1 · Answer 4 · answered Mar 01 '18 at 22:59

Get list of ID's from Section table for Instructors who have taught. Use NOT IN to see who has not taught any classes.

SELECT i.Salutation, i.First_Name, i.Last_Name, i.Zip
FROM Instructor i
WHERE i.Instructor_Id NOT IN (
    SELECT DISTINCT s.Instructor_Id
    FROM Section
)

Confusion over multiple row subquery comparison

4 Answers4