Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic trial
to watch this video

Sign up for Treehouse

Using a Subquery to Create a Temporary Table (Part 2)

5:33 with Andrew Chalkley and Kevin Nahm

Learn more about temporary, or derived tables.

Subqueries are one of the most advanced concepts to learn in SQL query writing. Seeing lots of examples and getting lots of practice will help solidify the concept.

Advanced Subqueries

As you begin using subqueries, be advised that a poorly structured subquery written against a large table or tables can impact overall query performance. Be careful if you are writing subqueries against data sets that consume tables with row counts in the million-plus row range.

Correlated Subqueries

This course does not cover the concept called Correlated Subqueries, which are slightly different in how they tie in with the outer query. Most of the time a regular subquery will do what you need, but correlated subqueries can be handy, too. These are especially heavy weight on the database engine, as they run the subquery many times; once per row in the outer query.

Information on all subquery types including correlated subqueries

Cheat Sheet

See all of the SQL used in Querying Relational Databases in the Querying Relational Databases Cheat Sheet.

  • 0:00

    In our last video, we were introduced to something called the temporary table or

  • 0:04

    derived table.

  • 0:06

    In this video, we'll show a more complex use of the derived table.

  • 0:11

    In our last example,

  • 0:12

    we used to derive table to filter results down to cars from the 2015 model year.

  • 0:18

    Similar to what we could do with an IN statement in the WHERE clause.

  • 0:22

    For this example, we want to see the data pivoted so

  • 0:25

    that it appears in columns side by side instead of being listed straight down.

  • 0:31

    We can use derived tables for this too.

  • 0:34

    Okay, first, I want to see the sum of sale amounts by sales rep and location.

  • 0:41

    To do this, we will use the tables sale,

  • 0:45

    sales rep, and location.

  • 0:48

    Sale will provide the data that we want to sum,

  • 0:52

    the sale amount, and we'll alias that as SaleAmount.

  • 0:56

    Sales rep and location will provide the contextual data that we want to group by,

  • 1:02

    the LastName and the LocationName.

  • 1:04

    We can get a good start like getting the decide data by using a standard GROUP BY

  • 1:09

    statement like this, but remember we want to display

  • 1:15

    the results as a comparison with the locations in the columns side by side.

  • 1:20

    We have two locations with sales data, Saint Louis and Columbia.

  • 1:26

    Let's break out the data into separate queries and rejoin them as sub queries.

  • 1:30

    First, we'll start off with a simple listing of sales reps as our base query.

  • 1:44

    Now, we want to create a query that will return all sales from the sale table,

  • 1:49

    for the Saint Louis location grouped by sales rep ID.

  • 1:54

    First, we need to SalesRepID.

  • 2:04

    Then, we want to sum the sale amount.

  • 2:09

    And alias it as StLouisAmount.

  • 2:22

    They St Louis location is the location ID of 1.

  • 2:33

    Finally, let's GROUP BY the SalesRepID.

  • 2:43

    This will now total the sales for each sales rep at the Saint Louis location.

  • 2:49

    Let's do the same for the Columbia location.

  • 2:54

    Let's copy and paste this query and replace Saint Louis with Columbia.

  • 3:01

    And the LocationID should be 2.

  • 3:04

    We can use these queries as subqueries.

  • 3:08

    We can use LEFT OUTER JOIN so we don't lose any sales reps.

  • 3:22

    Let's allies sub query say that we can refer to them elsewhere in the main query.

  • 3:27

    Let's name the first sub query Loc1 for location one and Loc2 for location two.

  • 3:34

    Let's cut and paste each of the queries in our subqueries.

  • 4:11

    Now, let's add the join criteria for both.

  • 4:35

    This SalesRep table has a SalesRepID, so that's ideal to join them.

  • 4:58

    Let's run this query now.

  • 5:04

    And make sure you've got the right table names.

  • 5:07

    Now we have the desired layout.

  • 5:09

    A side by side comparison of location cells breaking out by sales reps, nice.

  • 5:16

    When building complex queries like this, always feel free to

  • 5:20

    experiment with single queries first, then add complexity like we just did.

  • 5:25

    You're not expected to have this all figure out in your head first

  • 5:29

    before writing a single query like this.

You need to sign up for Treehouse in order to download course files.

Sign up