Retrieving dissimilar data using WHERE NOT in the joins?

Question

I need to retrieve the name of those departments in which no students are enrolled.

Table: department

dept_id    dept_name
      1    IT
      2    Electrical
      3    Civil
      4    Mechanical
      5    Chemical  

Table: stud_member

  f_name    dept_id   age
      AB         2      19 
    Rose         3      22
     May         1      20
    Noor         1      21
    Haya         1      19
    April        3      23
  Sakina         2      20

For example the names of mechanical and chemical. I have written this query for it using outer join (explicitly maybe?) But is shows an error.

please tell me that why i cannot write:

SELECT dept_id, dept_name
FROM department
LEFT JOIN stud_member ON (WHERE NOT department.dept_id = stud_member.dept_id);

I will be grateful if anyone will tell me the correct answer!

Answer 1

Assuming dept_id in stud_member can not be NULL which is true when dept_id is a FOREIGN KEY

SELECT dept_id, dept_name
FROM department
WHERE dept_id NOT IN (SELECT dept_id FROM stud_member);

as suggested using NOT EXISTS does not have this problem

SELECT d.dept_id, d.dept_name
FROM department d
WHERE NOT EXISTS (SELECT * FROM stud_member s WHERE d.dept_id = s.dept_id);

Answer 2

You can select all departments where Right part of Left JOIN is NULL

SELECT d.dept_id, d.dept_name
FROM department d
   LEFT JOIN stud_member sm ON d.dept_id = sm.dept_id
WHERE sm.dept_id IS NULL