SQL 201 – Sub Queries

Sub queries or nested queries are complete queries wrapped inside of another complete query. Some times this technique is used as a replacement for a JOIN statement. Some times it’s the only way to define exactly what you you want to get from a database. The important thing to learn about sub queries is they have their uses, but can be easily misused.

In my next post I’ll 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 subquery 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!

Previous: GROUP BY

Next: JOIN