Adam Waid Agile benefits Blogging Bob Galen branding Career Career Change Carol Hacker Community Consultant Craig Fisher Don Palmer Drupal Facebook Goals Hiring Hiring Manager Interview IT James Garvey Janna Mansker Jennifer Bradley Jennifer Kahnweiler Jobs Job search job seeker Justin Thomason Leadership LinkedIn Management Managers marketing MATRIX Matthew Schmitt Networking PM Project Management Project Manager resume Rick Sanders Sandy Jess Shannon Lowder Social Media SQL Stephanie A. Lloyd Success technology Twitter Willard Woodrow
3 days 13 hours
1 week 3 days
2 weeks 2 days
3 weeks 4 days
4 weeks 5 days
6 weeks 3 days
8 weeks 1 day
8 weeks 4 days
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.
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.
productName IN (
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!
productName IN (
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.