1 00:00:00,300 --> 00:00:05,010 Common table expressions are commonly used to refactor subqueries and 2 00:00:05,010 --> 00:00:06,390 create more readable SQL. 3 00:00:06,390 --> 00:00:09,710 A subquery, also called an inner query or 4 00:00:09,710 --> 00:00:15,200 nested query, is just another result set generated using the SELECT keyword. 5 00:00:15,200 --> 00:00:18,560 Subqueries can be used within a WHERE clause to filter results 6 00:00:18,560 --> 00:00:21,160 based on information from another query. 7 00:00:21,160 --> 00:00:26,710 For example, in this code, the subquery finds all car IDs from the cars table 8 00:00:26,710 --> 00:00:32,090 where a car's model year is 2015, in other words, it returns a list of different IDs. 9 00:00:32,090 --> 00:00:36,540 The WHERE clause then uses that list to further filter 10 00:00:36,540 --> 00:00:40,760 the sales data based on cars whose ID match the subquery. 11 00:00:41,790 --> 00:00:45,470 Subqueries are also used to create what are called derived tables, and 12 00:00:45,470 --> 00:00:47,580 are often used in a FROM clause. 13 00:00:47,580 --> 00:00:51,370 For example, this code uses two subqueries, 14 00:00:51,370 --> 00:00:54,850 the first creates a result set that acts just like a table. 15 00:00:54,850 --> 00:00:57,880 It has an alias name of all orders and 16 00:00:57,880 --> 00:01:03,700 is then joined to another subquery, which also acts like a table named, late orders. 17 00:01:03,700 --> 00:01:05,630 Using subqueries like this is useful, 18 00:01:05,630 --> 00:01:09,500 since it lets you organize your data into sets that are more useful for 19 00:01:09,500 --> 00:01:14,390 your data needs, but which aren't modeled in the actual schema of your database. 20 00:01:14,390 --> 00:01:15,480 The problem with this approach, 21 00:01:15,480 --> 00:01:19,450 however, is that the logic is buried midway through the query. 22 00:01:19,450 --> 00:01:23,610 It's kind of like reading a story in which the main characters don't appear until 23 00:01:23,610 --> 00:01:25,510 halfway through the book. 24 00:01:25,510 --> 00:01:28,590 A neater approach is to identify those derived tables 25 00:01:28,590 --> 00:01:30,250 at the beginning of your code. 26 00:01:30,250 --> 00:01:34,120 It's like introducing the main characters of your story in chapter one. 27 00:01:34,120 --> 00:01:38,420 Common table expressions let you do that, let me show you how. 28 00:01:38,420 --> 00:01:42,630 If you'd like to follow along, click the SQL Playground button on this page, and 29 00:01:42,630 --> 00:01:44,830 here's the code we just looked at. 30 00:01:44,830 --> 00:01:49,552 When I run it I get a list of employee IDs, names, 31 00:01:49,552 --> 00:01:54,340 a count of total orders, and a count of late orders for each of the employees. 32 00:01:54,340 --> 00:01:59,457 As I mentioned before, CTEs let you think more clearly about the data your after. 33 00:01:59,457 --> 00:02:03,212 Looking at the results here, I could say to myself, I need a list of all 34 00:02:03,212 --> 00:02:07,220 the orders, I also need a list of just the orders that were late. 35 00:02:07,220 --> 00:02:11,290 And finally, I need to match that data with my employees. 36 00:02:11,290 --> 00:02:15,040 Let's start by creating that first set, all orders, 37 00:02:15,040 --> 00:02:17,560 by converting the first subquery into a CTE. 38 00:02:18,680 --> 00:02:24,528 I'll just copy the subquery, and go to a new tab here, 39 00:02:24,528 --> 00:02:28,228 paste it in and convert it to a CTE. 40 00:02:28,228 --> 00:02:33,424 So I'll just say WITH all order AS, 41 00:02:39,857 --> 00:02:41,950 And that's our first CTE. 42 00:02:41,950 --> 00:02:44,800 You can have multiple commen table expressions in a query. 43 00:02:44,800 --> 00:02:49,590 And since it's original code had two subqueries, we'll have two CTEs. 44 00:02:49,590 --> 00:02:54,280 To add another CTE add a comma after the closing parenthesis of the first CTE, 45 00:02:54,280 --> 00:02:57,470 don't forget that comma, or you'll get an error. 46 00:02:57,470 --> 00:03:01,660 Also, don't use the WITH keyword a second time, you only need it once. 47 00:03:03,312 --> 00:03:07,903 To add the name of the second CTE, I'll just type late, 48 00:03:10,955 --> 00:03:15,426 orders AS, and add another set of parenthesis, and 49 00:03:15,426 --> 00:03:20,833 then I go back to my subquery, Copy that query. 50 00:03:24,199 --> 00:03:27,020 Now I want a list of employee IDs and names. 51 00:03:35,334 --> 00:03:37,879 So I'll select the employee ID and 52 00:03:37,879 --> 00:03:41,581 the employee last name from the employees table. 53 00:03:41,581 --> 00:03:46,787 Now remember these CTEs I just created act just like tables, 54 00:03:46,787 --> 00:03:51,498 so I can join them to my query just like any other table. 55 00:03:51,498 --> 00:03:56,710 So I'm gonna join employees to all orders on the employee ID. 56 00:03:58,590 --> 00:04:02,180 Then I'm gonna join late orders on employees IDs. 57 00:04:04,140 --> 00:04:06,111 Now I want a little bit more information. 58 00:04:11,150 --> 00:04:19,498 I want account of all the orders, And I want account of all the late orders. 59 00:04:23,851 --> 00:04:26,340 When I run the query I get the same results. 60 00:04:26,340 --> 00:04:30,290 Now this query is the same number of lines as the original code, but 61 00:04:30,290 --> 00:04:35,075 using CTEs, the code is much easier to read and think about. 62 00:04:35,075 --> 00:04:39,845 In the next video I'll show you how you can reference a CTE inside another common 63 00:04:39,845 --> 00:04:45,295 table expression to create complex queries that combine data in interesting ways. 64 00:04:45,295 --> 00:04:48,215 Feel free to close the SQL Playground, I'll have a new one for 65 00:04:48,215 --> 00:04:49,195 you in the next video.