All language subtitles for 5. Left Join

When we discussed inner join, we found out that only the intersection values are being returned back
1

by the query.
2

So basically inner join meant, finding out those values which are present in both the tables, Now suppose we
3

want that table A stays with us all values from table a are there.
4

And whenever there is some value in table B, It comes against that table A value, if it is
5

not available, we will get the null value.
6

If that is a requirement.
7

left join is used. So left join will return all values in the left table even if there are no matches in the right
8

table.
9

The syntax for this is.
10

Select.
11

Column from table one or table two, from table one left join table 2 so this is similar to inner join. only instead
12

of inner we are writing left join.
13

On then you give the field that you want to be common in both the tables
14

So what this will do is whole of the table A will be taken out and all the column values of table B
15

against table A will be plotted if there is any value in table A, which is not in table B will
16

get a null value for that particular column.
17

So, for example, we had the sales table, Suppose against the sales table, we want to plot the customer
18

information.
19

When we did the inner join, some of the sales table values were dropped because they were not present
20

in the customer table.
21

Now, if we want that none of the sales table values are dropped, all the values remain there and against
22

whichever there is customer value available, it should be plotted.
23

And whichever there is no customer information available, it should be it should remain as null, there
24

we will use left join as such, will.
25

write select all the column names, some from the sales
26

table, some from the customer table, from the sales table.
27

That will be table one for which we want all
28

the rows. Then left join with the customer table.
29

On the customer id which is a common field, Order by customer id
30

So let's go and write this query.
31

So for convenience, I have written down the customer IDs which are present in the customer table and
32

not present in the sales table, which are present in sales table not present in customer table and which are
33

present in both.
34

So that after we have written this query and we see the result, we can check finally, which is available
35

in the final table and which is not.
36

So let's start writing select.
37

The order line.
38

The product id.
39

The customer id
40

and the sales value.
41

From the customer table, we will require.
42

Customer name and customer age.
43

From the table sales 2015 as a and, left join with customer table as b so this is customer
44

2060.
45

As b.
46

On.
47

Customer id a.customer id is equal to
48

b.customer id
49

ordered by customer id
50

to if you scroll up, you can see that it is exactly same as the one written for inner join. Just as
51

the join is now left instead of inner.
52

Let's select this query and run it.
53

So you can see this, these are the values that we got.
54

So from the comment, you can check AA-10315 which is not present in the customer table,
55

is present in our result set, what this means is since we took left join on the sales table, all the
56

sales level values are available in this table.
57

So AA-10315 is part of sales table
58

So it is part of the Result Set.
59

Since the customer name and age values are not available for this customer Id in the customer table,
60

those are null.
61

The AA-10375 customer is available in both the tables, so that value is present in the ResultSet
62

along with the customer name and customer age, the AA-10480 is not available in this
63

result set, since this was not part of the sales table.
64

So basically in the left join, all values of table 1 are available and only the intersection values of table
65

two are available.
66

For the rows of table one, which are not available in table two, null values will be assigned in the result set.
67

So this is left join. One thing to notice here since our sales table had multiple values with the same customer id
68

you can see there are
69

So for this particular customer, there are four values four rows coming.
70

And against all these four rows, the same customer name and age is coming since our customer table
71

has customer id as the primary key.
72

If in the customer table, which is the table 2, so by chance in customer table, you have multiple values for
73

same customer id you'll be having multiple rows for each transaction of the sales table for each
74

row of the sales table
75

What I mean is so, for example, for this particular customer id 10375
76

We have two customers, Alan and John.
77

When we take left join, for this particular row, there'll be two rows in the ResultSet, one will have
78

Allen customer name and the other will have John as a customer name.
79

Similarly for the other three rows also, it will having two per row. Overall for this particular customer, you
80

would be having four rows in the ResultSet.
81

This may not be the desired result set for you.
82

So keep in mind, while using the left, join the matching key of the table.
83

Two should either be a primary key or should have the unique constraint.
84

This will give you the desired result in the next video we'll be discussing about the right joint.