It's not possible for me to show
that here, but it would be a wide table, and we will have every possible
combination of these records. So we will have Alex, So friends like IP
addresses and OSI models are fundamental building blocks of networking in the field of data. You can't go further enough if you don't have the basic understanding of SQL joins,
these joins are very basic, but then most of us struggle
to exactly understand how it works. And then while creating our queries,
it poses a lot of issues. So in this video we will understand five simple SQL joins which we use day
in and day out, and which help us do all kinds of data analysis,
data engineering activities. So friends for illustrating all these five SQL joins, we will take
these two sample tables. One is employee table,
and the other one is skill table. It contains the details of all the employees with the associated
skill ids which they possess. And then in the skill table, we have the
skill ideas mapped with specific skill name. So the first join, which is the most
common join is the inner join. And in the inner join we will take the intersection of employee
table versus the skill table. So to understand any join,
it's very good to use a Venn diagram. So we'll use a Venn diagram here. So for inner join,
we have these two tables and these two tables. Table, employee and table.
Skill table. We have this is the intersection
which we are taking. Suppose this is your employee table
and this is your skill table. And here you have how many records? Four records, and how many records
are there in this skill table 12345. 6 records, and how many you see common. So we can see that these
three IDs are present here. So these three records will come here
and then rest. All will be ignored. So this is how we use inner join. Now I will give a syntax
somewhere in the window here. So I'll exactly show you how that is. But we are talking about the logic here the syntax you can very
easily find on Google. But I will also try to put it either in the description or
somewhere in the screen. Okay, so inner join, make sure that you
take something which is common. So what would be the output of this
particular join condition? So friends, the output of the inner join between employee and the skill
table would be this. So we have only three records coming as we saw here, because these three records fell
in the intersection of these two tables. So now let's move on to our next join. So the next join is the left outer join. So in the left outer join, we take everything which is there
on the left table and only bring the results of those records
from the corresponding right table. So in this the Venn diagram
would show something like this. So what we are doing, we are taking everything from the left
table, which is employee table, and only bringing the records
which are common between the two tables from the right table,
which is skill table. So how many records are
here in the intersection? We saw that there are three records here.
Okay. And then you can see that Mohan is a record which has nothing
which has null in front of it. So it is maybe a fresher and he
does not have any skills as of now. So there is a null here. So there is no corresponding value in the skill table,
but because it is a left outer join, we will bring this
particular record as well. So in the output we will have four records, all the employee records
with this particular record from Mohan. So let's see the output. So as you can see here, this is the output of your left outer
join, everything from the left table and the corresponding data
coming from the right table. And for Mohan, it will be populated as Null
now let's move on to our next join. So the right outer join is exactly
opposite of left outer join, and it will have everything coming
from the skill table and the intersected value from the corresponding
left table, which is the employee table. So in this case we will have three records
coming in and then all the null values will be populated for the records which
are only present on the right table. So if we have to see the count, we will have these three intersected
records plus the additional three records which are present in this
particular table. So total six records. Okay, now in the full outer.
Very simple. It will have the combination
of the employee table as well as the skill table, and it will populate all
the records from both the tables. So as you can see in the Venn diagram,
the whole van is populated and coloured. That means we'll have this one additional record coming from here,
which is for Mohan. Then the three intersected records
for Alex, Maya and Radha, and then these three additional
skills of DevOps, Agile and CyberSec. As you can see here, Alex, Maya, Radha,
and then Mohan is populated with null, and then all the nulls here on the left
for the skills which are not present here. So full outer join combines both
the tables and populates the data wherever it is available and wherever
it is null, it populates null. So this is full outer join. Now let's go to our last join. So friends last join in our list is cross
join, which is also called as Cartesian product, which basically means that we
will have a multiplication or a Cartesian of every possible combination
of records between these two tables. So to illustrate this, we have
four records in employee table. So we have four records in employee table,
and then in Skills table, we have six records, so it will be
a product of 4 x 6 which is 24. So we will have 24 records. It's not possible for me to show
that here, but it would be a wide table and we will have every possible
combination of these records. So we will have Alex having six different entries
for all these six skills then Maya. So it will be four into six. Cross joins could be very costly, and it could also result in data
inaccuracy as far as my experience goes. So whenever you are facing some issues
with the performance or some issues with the data, you should always focus
and understand that whether by mistake, some sort of a cross join or
a Cartesian product is happening or not. Okay. But yeah, in some cases, it could
be a useful, viable option as well. So friends, this brings us
to the end of this video. We covered inner join, left outer right
outer, full outer and cross join. These are the basic joins. We have advanced joining
techniques as well. So if you want to learn that, let me know in the comments section and
I'll try to make a video on that as well. So if you're liking this data series,
please hit the like button. Let me know in the comments section some
suggestions which you would like me to cover in future videos,
and we'll continue to cover some basics of data engineering,
data analysis and overall data industry. So if you like this video and got some value out of it, please
subscribe to this channel. Hit the Bell notification. So you exactly know when
I upload my next video. Also, if you hit that like button,
it lets YouTube know that there is something useful going on on this channel
and it spread it to a wider audience. So until next time Guys Please keep learning. Keep sharing all your knowledge and yes,
keep hustling bye for now.
