1 00:00:01,110 --> 00:00:06,330 In this stage, we've learned about the SQL set operations and their four variants. 2 00:00:06,330 --> 00:00:06,995 Let's review. 3 00:00:06,995 --> 00:00:10,547 [SOUND] Each set operation has the same basic structure. 4 00:00:10,547 --> 00:00:16,210 The first query, then the set operation, then the second query. 5 00:00:16,210 --> 00:00:18,855 You can do anything inside of your queries that you'd normally do. 6 00:00:18,855 --> 00:00:22,663 Including, joining multiple tables together and 7 00:00:22,663 --> 00:00:25,590 filtering using a WHERE clause. 8 00:00:25,590 --> 00:00:29,156 You can select any number of columns, so long as you select the same number of 9 00:00:29,156 --> 00:00:31,285 columns in both your first and second query. 10 00:00:31,285 --> 00:00:35,827 [SOUND] You can also add an ORDER BY clause, but since you're ordering 11 00:00:35,827 --> 00:00:40,851 the final result set you only need to include it once, right at the very end. 12 00:00:40,851 --> 00:00:46,840 Unions combine all records from both query result sets into a distinct list. 13 00:00:46,840 --> 00:00:50,950 If there are any duplicates they are eliminated from the final result set. 14 00:00:50,950 --> 00:00:55,800 Like union, union all combines all records from both query result sets. 15 00:00:55,800 --> 00:00:58,010 However, duplicate rows are shown. 16 00:00:58,010 --> 00:01:01,020 Intersect compares the result sets from two queries and 17 00:01:01,020 --> 00:01:04,430 returns only the records that exist in both. 18 00:01:04,430 --> 00:01:08,564 Except compares the result sets from two queries and returns just 19 00:01:08,564 --> 00:01:13,386 the rows from the first query except for the rows that are also in the second. 20 00:01:13,386 --> 00:01:18,314 Set operations are structured in a very similar way and work by combining 21 00:01:18,314 --> 00:01:24,015 different data sets in various ways, but they perform very different functions. 22 00:01:24,015 --> 00:01:29,870 These SQL set operations are very useful tools to keep handy in your SQL tool belt. 23 00:01:30,890 --> 00:01:33,420 Once again it's time for you to practice. 24 00:01:33,420 --> 00:01:38,150 Open up the SQL Playground with this video and go through the various challenges. 25 00:01:38,150 --> 00:01:40,590 We're in the library database again but 26 00:01:40,590 --> 00:01:43,740 now we have two locations, North and South. 27 00:01:43,740 --> 00:01:47,460 Each location has books and loans associated with it. 28 00:01:47,460 --> 00:01:53,470 For example, books_north has all books in the library's northern location. 29 00:01:53,470 --> 00:02:00,100 The loans_south table contains all loan information for the southern location. 30 00:02:00,100 --> 00:02:02,030 Good luck and I'll see you in the next stage.