What is a Common Table Expression?6:31 with Dave McFarland
Learn the basics of common table expressions in SQL: what they are, why they're useful, and the basics of how to create them.
Basic Common Table Expression
WITH product_details AS ( SELECT ProductName, CategoryName, UnitPrice, UnitsInStock FROM Products JOIN Categories ON PRODUCTS.CategoryId = Categories.Id WHERE Products.Discontinued = 0 ) SELECT * FROM product_details ORDER BY 2, 1
Basic Common Table Expression Expanded
WITH product_details AS ( SELECT ProductName, CategoryName, UnitPrice, UnitsInStock FROM Products JOIN Categories ON PRODUCTS.CategoryId = Categories.Id WHERE Products.Discontinued = 0 ) SELECT CategoryName, COUNT(*) AS unique_product_count, SUM(UnitsInStock) AS stock_count FROM product_details GROUP BY 1 ORDER BY 2
[MUSIC] 0:00 [SOUND] Writing SQL to discover the information stored in a database is often 0:05 fun and rewarding. 0:09 It can also sometimes feel like you're working your way down a rabbit hole. 0:10 Creating complicated queries, joining multiple tables, and building complex 0:14 reports with aggregate data often requires a lot of crazy looking SQL. 0:19 What's worse is having to dig your way through the confusing logic of someone 0:24 else's query, that includes nested sub queries and lots of joins. 0:29 In this workshop, 0:34 I'll teach you how to make your queries easier to read, easier to think about and 0:35 easier to build using what's called a Common Table Expression or CTE. 0:39 Even better, CTEs are really simple to learn. 0:44 Most popular databases support CTEs, like Oracle, SQL Server, 0:48 PostgreSQL, Redshift, SQLite, and MySQL version 8 and later. 0:52 A common table expression is nothing more than an SQL query that you name and 0:58 reuse within a longer query. 1:03 It's a temporary result set. 1:05 Think of it as a temporary table. 1:07 Creating a common table expression is easy. 1:09 You place a CTE at the beginning of a complete query using a simple syntax. 1:11 Begin with the keyword WITH, followed by a name, the keyword AS, and 1:16 a SELECT statement inside of parenthesis. 1:20 The CTE name is like an alias for the results returned by the query. 1:23 You can then use the name just like a table name in queries that follow the CTE. 1:28 Lets look at a simple example. 1:33 If you'd like to follow along click the SQL playground button on this page 1:36 to open our online SQL tool. 1:40 I'll start with a simple query to retrieve data from a business database 1:43 containing product and sales information. 1:47 This query combines data from two tables. 1:49 I'll select a product name, a category name, a unit price, 1:53 and the number of units that we have in stock for that from the products table. 1:59 And then I join that to categories. 2:04 And I'll simply match up the category ID, 2:07 which is in the products table, to the ID in the categories table. 2:09 I also want to make sure that I'm only looking for products that we still sell. 2:14 In other words, products that are not discontinued. 2:18 When I run this, I get a complete list of products, 2:21 the categories they belong to, as well as pricing data. 2:24 Nothing that special, but as I said, a CTE is just a result set. 2:27 So I'll turn this into a CTE by adding the WITH keyword, a name, 2:32 AS, and wrapping the query in parentheses. 2:38 And to format this little bit, I'll indent inside the parentheses. 2:43 Now, when I run this, I get an error. 2:48 The code I just wrote only creates the common table expression. 2:51 To build a complete query, we must had a select statement to query it. 2:54 Like I said, the common table expression acts just like a table. 2:58 So you can query against the CTE name like this. 3:02 I'll select everything from product details. 3:06 And then I'll order it by the category name and the product name. 3:10 The name, product_details, 3:19 gives us a clear idea of what kind of data we're including in this query. 3:21 And since the CTE acts just like a table, we can query it just like a table. 3:25 For example, say I wanted to determine how many different products and 3:31 the total stock count for each category. 3:35 I can just modify the SELECT statement to get at that data 3:38 with the common table expression. 3:41 I'll select the category name but this time, I'll do a count. 3:46 And this will be the unique product count. 3:49 I'll also sum or total up the number of units we have in stock. 3:53 And I'll get that from our CTE product details. 3:59 I'll group them by category name, and then I'll order them by count. 4:03 This is really all there is to creating and using common table expressions. 4:12 But you might be wondering, why use a CTE? 4:16 These examples aren't all that exciting. 4:19 And you can get the same results with fewer lines of code. 4:21 That's true, and you probably won't use a CTE for 4:24 a short query like the one I just showed. 4:27 However, CTEs provide several key benefits for organizing long and 4:30 complex SQL queries. 4:34 First, your code is more readable using CTEs. 4:36 As I mentioned earlier, 4:40 the named query gives you information about the returned results. 4:42 Names like product details, active customers, or 4:45 recent transactions are clear and understandable when you see them. 4:48 Readable SQL is a big help for 4:53 other folks who have to work with your queries and a big help for 4:55 you when you later have to return to your SQL and remember how a query works. 4:59 Second, CTE's help you organize queries into reusable modules. 5:04 Look at the code we've written, I can easily just grab the CTE, 5:09 copy it and then paste it into a new query, knowing that I've got 5:14 all the relevant SQL needed to capture the product details I'm after. 5:19 Likewise, you can combine multiple CTEs into a single query. 5:23 I'll show you how in the next video. 5:28 So you could piece together CTEs almost like Lego blocks to create complex ways 5:30 to look at your database data. 5:35 Finally, organizing queries into common table expressions 5:37 matches how we think through data analysis problems. 5:40 We often think of our queries as result sets to begin with. 5:44 For example, say you wanted to figure out which product categories were most popular 5:47 with which customers in the past month. 5:52 You might start off with something like okay, 5:55 first I need all the orders in the past month. 5:57 Then I need all the active customers. 6:00 And finally, a list of all of our products and their categories. 6:03 It's natural to think of queries in that way. 6:08 And common table expressions help you write queries that match your 6:10 thought process. 6:13 For example, each comment here would represent one common table expression. 6:14 In the next video, I'll show you a common use for 6:20 common table expressions, converting subqueries to more readable code. 6:22 You can close the SQL playground here. 6:27 I'll have a new playground for you in the next video. 6:29
You need to sign up for Treehouse in order to download course files.Sign up