SQL Basics - Using Subqueries

Most databases use relational tables. What that means is that the data is not stored all in one table. It is split up into various tables. For example , one table would contain customer information, one would contain the order numbers, one would contain the products ordered, and yet another would contain the store products. All the tables are related in some way. The order table would have a column containing the customer that made it, and the order products table would have a column containing the order number it refers to.

To show an example, we'll use our good old database that we obtained at the beginning of this course. Let's say you have a recall on the Rabbit bean bag toy. It's a terrible choking hazard for little children. We now need to send a return envelope to all the customers that purchased it.

These are the steps we need to do.

1. Get the product ID of the rabbit bean bag toy from the Products table.

2. Get the orders that contain that item number from the OrderItems table.

3. Get the customer ID of the companies that made those orders from the Orders table.

4. Get the mailing information of those Customer IDs.

Now here's how we do it using subqueries, or nested SELECT queries.

SELECT (RTRIM(cust_name) + ' ' + RTRIM(cust_address) + ' ' + RTRIM(cust_city) + ', ' +RTRIM( cust_state) + ' ' + RTRIM(cust_zip)) AS 'Recall Candidates'

FROM Customers WHERE cust_id IN

(SELECT cust_id FROM Orders WHERE order_num IN

(SELECT order_num FROM OrderItems WHERE prod_id=

(SELECT prod_id FROM Products WHERE prod_name='Rabbit bean bag toy')))

If you follow through the SQL code, you will see that we have all those 4 steps nested one in the other. The outcome is below:

We used the RTRIM function to remove some of the spaces contained in the database. We used the AS function to name the outcome, as you can see atop the results table. We also used the IN function to choose multiple returned customers.

Next time we will learn a better way to do this when dealing with so many tables. We'll learn how to use JOINS. Until next time, keep warm.