Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll

Preview

Start a free Courses trial
to watch this video

Sign up for Treehouse

Convert a Subquery to a CTE

4:49 with Dave McFarland

Subqueries are powerful tools for writing complex SQL queries. Unfortunately, they can also lead to difficult-to-understand SQL. Learn how to make your SQL more readable and modular by converting subqueries to common table expressions.

Example Code

Subquery Example
SELECT 
  all_orders.EmployeeID, 
  Employees.LastName,
  all_orders.order_count AS total_order_count, 
  late_orders.order_count AS late_order_count
  FROM (
    SELECT EmployeeID, COUNT(*) AS order_count
    FROM Orders
    GROUP BY EmployeeID
  ) all_orders
  JOIN (
    SELECT EmployeeID, COUNT(*) AS order_count
    FROM Orders
    WHERE RequiredDate <= ShippedDate
    GROUP BY EmployeeID 
  ) late_orders
  ON all_orders.EmployeeID = late_orders.employeeID
  JOIN Employees
  ON all_orders.EmployeeId = Employees.Id
Rewritten with Common Table Expresssions
WITH all_orders AS (
    SELECT EmployeeID, COUNT(*) AS order_count
    FROM Orders
    GROUP BY EmployeeID
),
  late_orders AS (
    SELECT EmployeeID, COUNT(*) AS order_count
    FROM Orders
    WHERE RequiredDate <= ShippedDate
    GROUP BY EmployeeID 
)
SELECT 
  Employees.ID, LastName, 
  all_orders.order_count AS total_order_count,
  late_orders.order_count AS late_order_count
FROM Employees
JOIN all_orders ON Employees.ID = all_orders.EmployeeID 
JOIN late_orders ON Employees.ID = late_orders.EmployeeID

Common table expressions are commonly used to refactor subqueries and 0:00 create more readable SQL. 0:05 A subquery, also called an inner query or 0:06 nested query, is just another result set generated using the SELECT keyword. 0:09 Subqueries can be used within a WHERE clause to filter results 0:15 based on information from another query. 0:18 For example, in this code, the subquery finds all car IDs from the cars table 0:21 where a car's model year is 2015, in other words, it returns a list of different IDs. 0:26 The WHERE clause then uses that list to further filter 0:32 the sales data based on cars whose ID match the subquery. 0:36 Subqueries are also used to create what are called derived tables, and 0:41 are often used in a FROM clause. 0:45 For example, this code uses two subqueries, 0:47 the first creates a result set that acts just like a table. 0:51 It has an alias name of all orders and 0:54 is then joined to another subquery, which also acts like a table named, late orders. 0:57 Using subqueries like this is useful, 1:03 since it lets you organize your data into sets that are more useful for 1:05 your data needs, but which aren't modeled in the actual schema of your database. 1:09 The problem with this approach, 1:14 however, is that the logic is buried midway through the query. 1:15 It's kind of like reading a story in which the main characters don't appear until 1:19 halfway through the book. 1:23 A neater approach is to identify those derived tables 1:25 at the beginning of your code. 1:28 It's like introducing the main characters of your story in chapter one. 1:30 Common table expressions let you do that, let me show you how. 1:34 If you'd like to follow along, click the SQL Playground button on this page, and 1:38 here's the code we just looked at. 1:42 When I run it I get a list of employee IDs, names, 1:44 a count of total orders, and a count of late orders for each of the employees. 1:49 As I mentioned before, CTEs let you think more clearly about the data your after. 1:54 Looking at the results here, I could say to myself, I need a list of all 1:59 the orders, I also need a list of just the orders that were late. 2:03 And finally, I need to match that data with my employees. 2:07 Let's start by creating that first set, all orders, 2:11 by converting the first subquery into a CTE. 2:15 I'll just copy the subquery, and go to a new tab here, 2:18 paste it in and convert it to a CTE. 2:24 So I'll just say WITH all order AS, 2:28 And that's our first CTE. 2:39 You can have multiple commen table expressions in a query. 2:41 And since it's original code had two subqueries, we'll have two CTEs. 2:44 To add another CTE add a comma after the closing parenthesis of the first CTE, 2:49 don't forget that comma, or you'll get an error. 2:54 Also, don't use the WITH keyword a second time, you only need it once. 2:57 To add the name of the second CTE, I'll just type late, 3:03 orders AS, and add another set of parenthesis, and 3:10 then I go back to my subquery, Copy that query. 3:15 Now I want a list of employee IDs and names. 3:24 So I'll select the employee ID and 3:35 the employee last name from the employees table. 3:37 Now remember these CTEs I just created act just like tables, 3:41 so I can join them to my query just like any other table. 3:46 So I'm gonna join employees to all orders on the employee ID. 3:51 Then I'm gonna join late orders on employees IDs. 3:58 Now I want a little bit more information. 4:04 I want account of all the orders, And I want account of all the late orders. 4:11 When I run the query I get the same results. 4:23 Now this query is the same number of lines as the original code, but 4:26 using CTEs, the code is much easier to read and think about. 4:30 In the next video I'll show you how you can reference a CTE inside another common 4:35 table expression to create complex queries that combine data in interesting ways. 4:39 Feel free to close the SQL Playground, I'll have a new one for 4:45 you in the next video. 4:48

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

Sign up