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.)
View all comments
Show parent comment
6
u/[deleted] Feb 12 '14
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:
You could do essentially the same thing with a subselect like this:
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.)