All language subtitles for 4. Inner Join

So let us learn what is in a join.
1

So Iner join will compare each row of both the tables and then find out the pros, which are satisfying the conditions
2

that we have mentioned, once it is identified, the rows, it will provide us, the column values of both the
3

tables that we have specified in this select statement.
4

In other words, inner join, finds out the intersection first.
5

So, for example, if you have a group which contains ABCD and you have another group which contains
6

DEFG, if you find the inner join of these two groups, you'll find the intersection point, which is
7

only D, which is present in both the groups.
8

So this is inner join. With this graphic
9

Also, you can see inner join is the shaded part, which is common between the two tables.
10

So it will find out that shaded part first and then for that shaded part, it will give you the values of
11

all the columns that you have mentioned in the select clause.
12

Let us look at the syntax of this inner join, we will provide the column names in the select from table
13

one, then we'll write inner join, table two.
14

And then we will give the condition, the joint predicate, which is start with on, table one dot the column
15

which we have to match table two column.
16

When we are specifying the columns also, since there are two tables involved, we will need to specify
17

the table also then the column name.
18

Let us look at this from the example.
19

Now, suppose in a table of sales where we have the sales of 2015, if I want to plot the name of the
20

customer.
21

And the age of the customer against each order line, how do I do that?
22

Inner join will help me in that.
23

So I will write select
24

A dot order line. So a is an alias here for the table since 2015.
25

If you remember, we assigned aliases to column names in the video titled AS, we are using the same
26

AS keyword to now provide Alias to a table such as sales, 115 has been given alias of a and customer
27

2060 has been given an alias of b. So when I'm writing these names, I can either write sales_2015
28

dot order_line and then sales_2015 DOT product underscore id every time.
29

Or I can just provide a short alias of a, and write a.orderline, a.product id
30

and so on.
31

So I want the order line, product id, customer id, the sales value and the customer name and customer
32

age.
33

So all these six columns are part of my select clause.
34

From has table one name inner join table two name.
35

On this will be the condition, which will be matched with both the table, So it will be a dot customer id
36

is same as b dot customer id
37

So the customer is same in both the tables, only when this condition is met, all these six values
38

will be taken out and provided to us in the ResultSet.
39

And will be ordering this by the customer id, let us go and write this in the pgAdmin.
40

So we will select.
41

From the first table, we want the orderline
42

I'll correct spelling of order, then a dot product id
43

then a dot customer id.
44

Then a dot sales
45

B dot customer name and b.age
46

These are the six columns now, I'll specify the table name from table 1. Table 1is
47

Sales 2015 As a
48

inner join
49

table 2 name customer_20_60 under 60 as we.
50

On a .customer id is equal to b.customer id
51

Order by
52

customer id
53

So select this query and run it.
54

So you can see that I have got the order line, product id, customer id and sale value form the
55

first table, which is the sales table and customer name and age from the customer table where customer
56

id is same in both cases.
57

So just to confirm that the result in table is actually intersection of both the tables, we will find
58

out some particular customer IDs, which are not part of any one of the tables and confirm that they
59

are not part of the result.
60

table also. So let us find out the customer IDs in the sales table.
61

So let us remember, 10315 and 10375
62

These two customer Ids are in the sale table. Now if I run the second select statement.
63

So if you remember, one zero three one five is missing in the customer table.
64

10375 is in the customer table, so it should be present in the intersection also.
65

Let's run the query that we created earlier.
66

So you can see one zero three one five is missing from this table, so it was part of only one table,
67

but one zero three seven five is part of this table since it was present in both the tables.
68

So this is giving us the intersection of the two tables
69

Similarly, we can find another customer, right, which is part of the customer table, but not part
70

of the sales table.
71

So, for example, this one zero for the customer is part of customer table.
72

And let us see in the sales table, I think this is missing in this table.
73

So 10480 customer id we will check if it is present in the inner join table.
74

So you can see one zero four eight zero is missing from this table since it was written in only the
75

second table , this confirmed that it is giving only the intersection part of the two tables.
76

That is inner join for you. In the next election.
77

We will look at Left join.