NOT EXISTS works the opposite as EXISTS. The WHERE clause in NOT EXISTS is satisfied if no rows are returned by the subquery. The following example finds employees who are not in departments which have names that start with P.
Example #1
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE NOT EXISTS (SELECT NULL)
ORDER BY Name ASC
Example # 02
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE NOT EXISTS
(SELECT *
FROM HumanResources.Department AS d
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.EmployeeID = edh.EmployeeID
AND d.Name LIKE 'P%')
ORDER BY LastName, FirstName
Example #1
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE NOT EXISTS (SELECT NULL)
ORDER BY Name ASC
Example # 02
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE NOT EXISTS
(SELECT *
FROM HumanResources.Department AS d
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.EmployeeID = edh.EmployeeID
AND d.Name LIKE 'P%')
ORDER BY LastName, FirstName
