Not Exists in Correlated Subquery in SQL Server (Part -13)
EXISTS and NOT EXISTS are used with a subquery in WHERE clause to examine if the result the subquery returns is TRUE or FALSE.
The true or false value is then used to restrict the rows from outer query select.
Because EXISTS and NOT EXISTS only return TRUE or FALSE in the subquery,
the SELECT list in the subquery does not need to contain actual column name(s).
Normally use SELECT * (asterisk) is sufficient but you can use SELECT column1, column2, column3 or anything else.
It does not make any difference.
Because EXISTS and NOT EXISTS are used with correlated subqueries, the subquery executes once for every row in the outer query.
In other way, for each row in outer query, by using information from the outer query, the subquery checks if it returns TRUE or FALSE, and then the value is returned to outer query to use.
Example 1: Using NOT EXISTS in correlated subquery:-
/*
This query uses NOT EXISTS keyword in WHERE clause
to return a list of customers whose not given any order.
Note that this query returns two more rows than
the query in Example other. This is because Customer
PARIS and FISSA do not have records in orders table.
*/
select CustomerID, CompanyName from customers as a
where not exists
(
select * from orders as b
where a.CustomerID = b.CustomerID
);
/*
This query uses left join and returns the same result as the query above. The left join returns all records from the customers table and included the customers who have not placed any orders - PARIS and FISSA.
*/
select c.customerid,o.customerid from customers a left outer join orders b on a.customerid=b.customerid
where b.CustomerID is null