JOIN clause SQL-CodePudding

I need help with a problem I have to solve with SQL.

Using -JOIN I have to display the students that were not enrolled in any courses. Using two tables: db1.ncc.Student and db1.ncc.Registration. Student table has 4 students and in table registration there's only three out of those four.

In my last attempt to solve this I tried using a -LEFT JOIN to get all the matches and the unmatched student from the student table then I used -WHERE to try and filter the results to only the unmatched student.

I was unsuccessful and after trying many other ways previous to this one I've given up and started seeking some help.

select 
    STUDENT.StudentID
    , STUDENT.StudentName
from db1.ncc.STUDENT left join db1.ncc.REGISTRATION on STUDENT.StudentID=REGISTRATION.StudentID
where REGISTRATION.StudentID<>STUDENT.StudentID

note: I have to use join to do this

CodePudding user response：

You are almost there. The LEFT JOIN is the good approach, but then in the WHERE clause you want to check the the join did not match. The inequality check does not work because the "missing" value is NULL, and NULL != ... is always false.

Instead, you can explicitly check for the nullity of the registration column:

select s.StudentID, s.StudentName 
from db1.ncc.STUDENT s
left join db1.ncc.REGISTRATION r on r.StudentID=s.StudentID 
where r.StudentID IS NULL

Side note: table aliases (here: s and r) makes queries easier to write and read.

CodePudding user response：

You were close but the issue is if the student is not on Registration then there isn't a match, thus it is NULL, thus you can't use operators like =, <>, != ...

select STUDENT.StudentID, STUDENT.StudentName from db1.ncc.STUDENT
 left join db1.ncc.REGISTRATION 
   on STUDENT.StudentID=REGISTRATION.StudentID 
where REGISTRATION.StudentID IS NULL;

PS: This should be a hobby thing, otherwise LEFT JOIN for this purpose is not suggested.