Subqueries

Sometimes it may not be possible to retrieve the results you need using a simple SELECT statement. At times, you might need to create a SELECT statement and compare the results to that of another statement. In that case, you would want to use subqueries. A subquery is a query nested inside another query. There are two types of subqueries you can use:

• The IN operator

• The embedded SELECT statement

The IN Operator

The IN operator is used in a SELECT statement primarily to specify a list of values to be used with a primary query. A classic example is if you wanted to find all your employees who lived in California and its border states such as Arizona, Nevada, and Oregon. You could write a SELECT statement using the IN operator to accomplish that:

 SELECT *  FROM Employees  WHERE BillingShippingState IN ("Ca", "Az", "Nv", "Or") 

This statement effectively returns all the employees who live in the states of California, Arizona, Nevada, and Oregon.

The Embedded SELECT Statement

An embedded SELECT statement is used when you want to perform a secondary query within the WHERE clause of a primary query. Suppose that you wanted to see a list of employees who have completed orders for the week. If that were the case, your query might look like this:

 SELECT *  FROM Employees WHERE EmployeeID IN          (SELECT DISTINCT EmployeeID         FROM Orders)