SQL Left Join Queries Difference

Question

tblEmployee Table Left and tblDepartment Table Right

First Query:

Select Name, Gender, Salary, DepartmentName
from tblEmployee
Left Join tblDepartment
On tblEmployee.departmentID = tblDepartment.Id
Where tblEmployee.departmentID IS Null; 

Second Query:

Select Name, Gender, Salary, DepartmentName
from tblEmployee
Left Join tblDepartment
On tblEmployee.departmentID = tblDepartment.Id
Where tblDepartment.Id IS Null

The two queries I wrote above are used to display the data in the second picture (the one with only two rows). Can someone explain to me why both the queries above produce the same results? I understand why the first query works since you are simply filtering out all the records where departmentID is not equal to NULL and selecting the ones where departmentID is equal to NULL. Though for the second query I don't understand the idea behind the where clause. How does it filter out those two records in the Employee table that are NULL?

Answer 1

First Query:

Select Name, Gender, Salary, DepartmentName
from tblEmployee
Left Join tblDepartment
On tblEmployee.departmentID = tblDepartment.Id
Where tblEmployee.departmentID IS Null; 

Will bring back results from A and B where A does not have a departmentId

So: James and Russell fit the description.

Second query:

Select Name, Gender, Salary, DepartmentName
from tblEmployee
Left Join tblDepartment
On tblEmployee.departmentID = tblDepartment.Id
Where tblDepartment.Id IS Null

Will bring back results from A that do not exist on B.

So: James and Russell fit the description.

Answer 2

In the second query, the tblDepartment.Id will be NULL for the last two records, because it cannot find the corresponding record in the tblDepartment table. Left Join will return all the rows from the first table. If it cannot find the values in the join, the values of the columns from the right table are replaced by NULL. Hence you get only last 2 records.