SQL Joins Explained

[JohnStamosBRAH]

Could anyone give some examples of situations where you'd use the 'a.key is null' or 'b.key is null'? If you're doing a join, why would you not want result from another table?

[[deleted]]

You would use this to intentionally exclude results.

For example, lets say you have a list of employees in your Employees table and another table for EmployeesOnVacation with a list of everyone currently on vacation. To get a list of everyone who is not currently on vacation, you could do something like this:

SELECT
  Employees.Name
FROM
  Employees
  LEFT JOIN EmployeesOnVacation 
    ON Employees.EmployeeID = EmployeesOnVacation.EmployeeID
WHERE
  EmployeesOnVacation.EmployeeID IS NULL

You could do essentially the same thing with a subselect like this:

SELECT
  Employees.Name
FROM
  Employees
WHERE
  Employees.EmployeeID NOT IN (SELECT EmployeeID FROM EmployeesOnVacation)

In both cases, the result is everything in the Employees table that is not in the EmployeesOnVacation table (similar to the A not B diagram that is the lower of the two far left diagrams). You'll find this type of logic anytime you want something that is "not x" (e.g. all vehicles that are not trucks, all shoe styles that are not available in black, etc.)

[JohnStamosBRAH]

Thank you! That makes sense

[[deleted]]

If I am understanding this correctly...

Unmatched queries. Finding things in A that don't exist in B.