Original subtitles

0 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.