Subqueries in SQL

A very helpful feature of SQL is the use of subqueries.  These subqueries allow you to nest one query inside of another query, thus giving you more flexibility with your data.  There are two areas in which a subquery can be used.  The first of these areas is in the SELECT statement.  For example, if we wanted to see how many purchases a customer has made, we can run a query like this:

SELECT CustomerNumber, CustomerName,
(SELECT SUM(Quantity)
FROM Orders
WHERE Orders.CustomerNumber = Customer.CustomerNumber) as Quantity
FROM Customers

In the above example, we used a subquery on the Orders table to get the total quantity (number of purchases) and then the Customers table to get our customer information.

The second instance where a subquery can be used is in the WHERE clause.  This gives us the ability to look for a particular set of data.  As another example, let's say that we want to look for customers that have an order greater than $50:

SELECT *
FROM Customers
WHERE CustomerNumber in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

The subquery returns all CustomerNumbers that have an order of $50 or greater.  We then tell SQL to look for all those (in) CustomerNumbers in our Customers table.  We could also tell SQL to give us CustomerNumbers that are not in the list by putting the word "not" in front of "in, like so:

SELECT *
FROM Customers
WHERE CustomerNumber not in
(
SELECT CustomerNumber
FROM Orders
WHERE TotalPrice >= 50.00
)

This would return all customers that did not have an order of $50 or more.  The word "in" in this situation is called a "logical operator", which we will discuss in my next blog.

To read more on subqueries, go here: Subquery Fundamentals