Original subtitles

0 In this video, I will be covering the theoretical concept behind joining tables. 1

Why this is done and how it is used. 2

We will then cover each and every type of joint and their implementation in the upcoming videos. 3

Let me take an example business scenario. 4

Suppose the management wants to know about the performance of each region so that they can decide on 5

the marketing budget for each region. 6

So they ask you to find the total sales done in each region. 7

There are two things I need for this. 8

First, I need the sales data, which is in the sales table. 9

And second, I need the region, which is part of the customer table. 10

If we could somehow assign region values to each of these transactions. 11

That is, if we could assign that this transaction. 12

Belongs to the south region. 13

And this transaction belongs to the West region. 14

Then we can simply aggregate the data on these regions to find the of sales for each region. 15

But how do we know the region for this transaction? 16

You must have noticed that there is one field common between these two tables. 17

That is the customer, I'd call them. 18

Using this custom I'd, we can match the customer who did this transaction to which region this 19

particular customer belongs. 20

This is the concept of joins, you can join the information present in two or more tables using a 21

common key column or a group of columns. 22

We can also join more than two tables, for example, here we can join sales table and customer table 23

based on customer I'd, and we can join sales table and product table based on product I'd. 24

Once we have this join data, we can then find things like how much our customers from the state of 25

California spending on each of the product category. 26

Now, let us see what we need to know so that we can create join data from two tables. 27

First, we need to know the name of the tables that we want to join. 28

For simplicity's sake, let's say we are joining only two tables, so we need to know the name of these 29

two tables. 30

So this is our sales table and this one is our customer's table. 31

Next, we need to know the common column based on which we will join these two tables. 32

For example, here, the common column was customer id. 33

Lastly, we need the list of columns that we want from each table in our join data. 34

Suppose we are trying to find out region wise sales, we can choose to have all columns from both tables 35

or we can choose which columns we want from each table so I can choose only order I'd customer I'd and 36

sales value from the sales table. 37

And region value from the customer table to get a table like this and then use it to find the region 38

wise sales 39

This output will be lighter. 40

But this will have limited functionality only. 41

So which columns should come in, the output from which table, this is an option that we have while 42

joining the tables 43

Now, let me tell you about the different types of joins. 44

We will cover each type of join in detail in the coming videos, but here I just want to explain the 45

logic behind all these different types of joint. 46

For the purpose of this example, suppose this is my sales data, I have taken only a few rows and columns 47

so that we can clearly see what is happening. 48

Now we want to join these two tables based on the Common key, which is the customer I'd. 49

Let us look closely at the customer IDs in these two tables. 50

What do we notice, We see that these customer IDs CG-12520, DV-13045, 51

and SO-20335, these three customer IDs are present in both the tables. 52

This customer I'd PG-18895 53

This is present only in the sales table and it is not present in the customer table. 54

And this I'd BH-11710 is present in customer table, but not in the sales table. 55

Now, we have four options here, which results in four types of joins. 56

The first option is that in the result, we get only those results for which customer Id is present 57

in both the tables. 58

So in the first point, we have these three customer Ids which are present in both the tables. 59

So in the result, we will get only these three customer ids and all the data from both the tables. 60

So you can see we have order line, order Id, order date, customer id, product id and sales value, all of these 61

columns are coming from the sales table and these three columns, customer names, state and region. 62

This is coming from the customer table. 63

So you can see that the data is joint of the two tables, but the number of rows are less. 64

Only those rows are there for which we have data from both the tables. 65

This type of join is called inner join, and the name is coming from this Venn diagram. 66

This circle, the present customer IDs of sales table, this second circle represents the customer ID 67

of customer table. 68

This center shaded part is those customer IDs, which are present in both the labels. 69

This is the inner part of the venn diagram, so we are calling it inner join. 70

Other option is if we decide that in the output, we want all the customer I.D. from the sales table. 71

They may or may not be present in the customer table if they are present in the customer table, 72

For example, the first id CG-12520, this is present in the customer table also 73

Also, if it is there, we will get the customer data. 74

But if that customer ID is not present in the customer table, for example, this ID 75

PG-18895 76

Since this is not available in the table, we will still have it in the output and in the places where 77

we are getting the customer data. 78

We will have null value. 79

So you can see the output of left joining, we have sales data for all these ids because we are getting 80

all the Ids from the sales table. 81

But for that particular id Where we do not have the customer data. 82

The data is null. 83

You can see the Venn diagram for left join. 84

All the customer ids from sales table are picked so that is shaded 85

But those customer IDs, which are not part of the sales table, those are left out. 86

So since the left part is shaded, this is called left join. 87

Similar Is right join. 88

If you want all customer I.D. from customer table. 89

Even if no data is available for some Ids in the sales table, we can do a right join. 90

This is the output, if we do a right join we will not have sales data for this particular ID 91

BH-11710 because this is not present in the sales table. 92

But this will be part of the output because it is in customer table and we are doing a right join. 93

You can look at the Venn diagram. This time, we are taking all customer Ids from customer table and excluding 94

those I'ds which are not present in the customer table. 95

Fourth option is if you want to include all customer Ids present in both the tables in the situation, 96

the result looks like this. 97

We have all the customer ids for the customer Id where. 98

We do not have customer data, there 99

We get null for customer details and for that customer Id 100

Where we do not have sales data. 101

We get null for the sales transaction details. 102

You can see the venn diagram. 103

All the parts are shaded. 104

This is why this particular join is called full outer join or full join. 105

Now, one question may come to your mind if we keep sales data on the left and do left join. 106

Or if we keep sales stable on the right and the right, join, will these to give same results. 107

Short answer is yes, but once we have covered the topics in detail, you should try it out yourself 108

to confirm that this holds true. 109

There is one more join called Cross Join, but that is quite different from these ones, so we will 110

learn more about it in its respective video. 111

So this was about joining two different tables with different data, using a common column. 112

There is another way of combining data, which is combining the same type of tables. 113

For example, suppose the online team saves customer data in one table, so, for example, these four 114

customers are online customers and the second table is showing our offline customers. 115

So these three are the offline customers list. 116

Now, to get data of all the customers, we may want to combine the entries in these two tables, this 117

type of combining data is handled by operators like Union Accept and intersect. 118

These are called combining inquiries. 119

So this is different than joining this is combining the data and these queries will be discussed after 120

we have discussed the joining queries. 121

With this background about the topic, let's jump right in and see each of these joining and combining 122

queries in detail. 123

Any questions coming to your mind now will most probably get answered in these upcoming videos? 124

I'll see you in the next video.