All language subtitles for 9. Intersect and Intersect ALL

Till now, we were trying to join two completely different tables based on a common key.
1

Now we are going to discuss about combining queries so there are certain operators which can help us
2

combine the output of multiple queries.
3

For example, if we have one select query giving us one output and another select query giving us another
4

output, if the structure of the output of the two queries is similar, then we can combine the results
5

of these two select queries using certain operators.
6

Now these operators can be any of these intersect except and union.
7

Let me show you an example so that you understand the difference between Intersect except and union,
8

so suppose from one query I am getting this result, it is giving me a result of 5 customers and their
9

ages.
10

And there is another query which is giving me a list of another five customers and their age.
11

Now you can note that out of the five customers in the first table, these third and fourth customers
12

are also present in the result of the second query.
13

So these two are common.
14

You can also see that the structure of the output from both the queries is same.
15

They both have two columns and the first column contains customer name, which is textual, and the
16

second column contains age, which is numeric.
17

So I can combine the result of these two queries.
18

Again, just like join.
19

I have multiple options of how I want to combine these two results.
20

The first option is I want only those results which are present in both of these query output.
21

So these third and fourth customers, which are present in both the queries, they will come if I use
22

Intersect.
23

So Intersect will give me those rows which are present in both the output.
24

The order does not matter.
25

So both of them do not need to be at the third or fourth position, only that this customer should be
26

present in both the outputs.
27

So Intersect will show me all the customers which were present in both output.
28

Then we have except. Except will remove the common customers from the first output.
29

So if I have the first output, which is this, it will look at the second output and compare each customer
30

name.
31

Whichever customer name is also present in the second output, those will be removed from the first
32

output and then we will get the remaining names from the first output.
33

So in the first output I had these five names, third and fourth were also present in the second output.
34

So those were removed and I am getting remaining three names in my output. Union combines the result
35

of both the outputs and you can see that I am getting all the names from the first table and the names
36

from the second table also.
37

So intersect will give me only those outputs which are present in both the tables, except will remove the
38

output which are present in both
39

the table. Union will show me the combined output of all the select queries.
40

Now, let us discuss each of them, one by one in detail, so Intersect operator, as I have said,
41

will return the common rows from the results of two or more select queries.
42

The syntax goes like this.
43

You have one select query.
44

You can select multiple columns from table one.
45

Then you have another select query where you have to select the same columns from table two and you
46

can find the intersection of these two select queries using this Intersect.
47

Operator, we can have more select queries also and we can add more intersect operators also. So if we
48

have more select queries and more intersect operators, it will find the intersection of all those select
49

queries.
50

So here's an example, we have the sales 2015 table and we have customer 2060 table, there are some
51

customer ideas present in the sales 2015 table, some customer ideas present in the customer 2060 table.
52

If I find intersection, I'll get the customer Id's which are present in both, sales
53

2015 table and customer 2060 table.
54

So let's go to pgAdmin and write this query to see those customer ids which are present in both
55

the tables.
56

So here we will write select
57

customer id.
58

From Sales 2015.
59

Intersect, select.
60

Customer ID.
61

From.
62

Customer
63

underscore 20_60 select this query and run it.
64

So you can see that 436 customer ids are such which are present in both sales 2015 and customer 2060 table.
65

And this is a list of those customer ids.
66

Now, there is a variation of Intersect, which is called intersect all so if you have duplicate values
67

in the sales and customer table, you can use intersect all.
68

And this is available with Union also, So there is Union and union all. Union will give us combined
69

output, but it will remove duplicates, but union all will keep the duplicates
70

also. We will discuss union in detail in the upcoming videos.
71

In the next video, we will discuss about Except Operator and then we will talk about Union Operator.
72

See you in the next video.