How To Test Subquery Results with the EXISTS Operator?

EXISTS is a special operator used to test subquery results. EXISTS can be used in two ways:

EXISTS (SELECT ...)
-- Returns TRUE if the specified subquery has one or more rows returned.

NOT EXISTS (SELECT ...)
-- Returns TRUE if the specified subquery no rows returned.

Example:

-- Number of customers with orders
SELECT COUNT(*) FROM SalesLT.Customer c WHERE
EXISTS
(SELECT * FROM SalesLT.SalesOrderHeader s WHERE s.CustomerID = c.CustomerID)


-- Number of customers without orders
SELECT COUNT(*) FROM SalesLT.Customer c WHERE
NOT EXISTS
(SELECT * FROM SalesLT.SalesOrderHeader s WHERE s.CustomerID = c.CustomerID)