1. Using subquery to return a single value
2. Using subquery to return a list of values
3. Using subquery to return one ore more rows of values
4. Using correlated subqueries
5. Using EXISTS and NOT EXISTS with correlated subqueries

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, ... 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 words, 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.

Practice #1: Using EXISTS in correlated subquery.

By examining the query in this practice, we can sum up the following steps that the database engine takes to evaluate the correlated subquery. It demonstrates that the subquery uses data from the outer query and the subquery executes once for every row in the outer query.

1. The outer query passes a value for CustomerID to the subquery. It takes place in the WHERE clause in the subquery.

2. The subquery uses this passed-in CustomerID value to look up ShipCountry of UK in orders table.

3. When a matched row is found, the subquery returns the value TRUE to the outer query.

4. The outer query returns the CustomerID and CompanyName for this row in Customers table.

5. The query engine then moves onto next row in the Customers table and repeat Step 1 to 4 again for the next customer.

6. When all customers in Customers table have been evaluated, it returns the query result.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
This query uses EXISTS keyword in WHERE clause 
to return a list of customers whose products 
were shipped to UK.
 
Note that the outer query only returns a row
where the subquery returns TRUE.
*/
select CustomerID, CompanyName 
from customers as a
where exists
(
    select * from orders as b
    where a.CustomerID = b.CustomerID
    and ShipCountry = 'UK'
);
 
/*
This query uses INNER JOIN and returns the same 
result set as the query above. It shows you that
the correlated subquery can be rewritten as join
operation.
*/
select distinct a.CustomerID, a.CompanyName 
from customers as a
inner join orders as b
on a.CustomerID = b.CustomerID
where b.ShipCountry = 'UK';

Query result set - 7 rows returned:

Practice #2: Using NOT EXISTS in correlated subquery.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
This query uses NOT EXISTS keyword in WHERE clause 
to return a list of customers whose products were 
NOT shipped to UK.
 
Note that this query returns two more rows than
the query in Practice #1. 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
    and ShipCountry <> 'UK'
);
 
/*
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 distinct a.CustomerID, a.CompanyName 
from customers as a
left join orders as b on a.CustomerID = b.CustomerID
where b.ShipCountry = 'UK' or b.ShipCountry is null;

Query result set - 9 rows returned: