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.