SQL 101: Sub Queries

In my next post I will introduce you to JOIN statements. After you've been introduced to both these techniques consider the performance of a sub query against the performance of a JOIN. You will always want to choose the option that runs quickest with the correct results.

If you would like to create my test table on your own server, run this script.

There are two types of sub queries, Correlated and non-Correlated. Until you learn about joins, I'm only going to teach you about non-correlated queries.

Since a sub query is one complete query inside another. Let's start by writing a query to show us all the productNames in the productSale Table.

SELECT DISTINCT
       productName
FROM productSale

This is a complete query. Now, if we take that query we can set up a query that will show us what purchaseDates these were purchased.

SELECT DISTINCT
     buyer
FROM productSale
WHERE
productName IN (
      SELECT DISTINCT
           productName
       FROM productSale)

buyer --------------
Shannon Lowder

Please notice that the comparison used is the IN comparator. Since the sub query returns multiple values you must use IN. If your query will only ever return one value, you could use =, etc.

The query now shows you my name, since I'm the only buyer defined in the table. What this technique will allow you to do is find data in one table, based on another table. This is where you will see people use a sub query instead of a JOIN.

While it's not wrong, it can take longer to execute than a JOIN.

Consider the following query, if you run the attached script on your SQL server, you can see the output for yourself!

SELECT *
FROM productSale
WHERE
              productName IN (
              SELECT
                         productName
               FROM products
               WHERE
                          price <= '1.00')


buyer                       productName      purchaseDate       qtypurchased  pricePaid
--------------  -----------  -----------------------  ------------  ---------
Shannon Lowder    paper        2000-01-01 00:00:00.000  2                1.00
Shannon Lowder    pencil        2000-01-05 00:00:00.000  1                0.25
Shannon Lowder    pencil        2000-01-07 00:00:00.000  1                0.25

The sub query will retreive all the productNames in the products table where the price is less than or equal to a dollar. Then the main or outer query lists all the information in the productSale table where the productName matches. This is definitely a query where a JOIN could be used, and next time we will rewrite this query to use a JOIN. But in this case it clearly shows you how to write a subquery to retrieve information in one table, based on values from another.

If you have any questions, send them in! I’m here to explain everything I can about SQL, and how to use it more effectively!

About the Author: 

Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan. Connect with Shannon Lowder.

Posted in: 
Development
Bookmark and Share