SQL tutorial 28: Left Outer Join By Manish Sharma / RebellionRider

what's up internet how you doing I'm back once again with another SQL tutorial and this tutorial is all about left outter join I made the last tutorial in a real hurry so please don't be angry if you found that tutorial difficult to understand then comment below and let me know I'll make that tutorial again after all whatever I'm doing on this YouTube channel is to help all you guys only but don't dislike that tutorial please okay so let's start today's tutorial we will be using both the same table for example EMP and D which we created in the last tutorial and I will try to make this tutorial as easy to understand as possible this time we will start with the syntax of left outter join first we have our selected statement where you can specify column names from all those table which are participating in your join query and these column names are followed by keyword from then we have our join clause which is the left outer joint and on both the side of our join clause we have our tables which are the source and Target table as we are talking about left outer joint thus the table which is on the left side of our joint Clause is the source table and the one on the right side is the target table followed by our joint Clause we have our joint condition basically we have two types of joint condition that we can use one at a time but never together these two joint conditions are on and using uses of each joint condition depend on certain conditional parameters don't worry we will discuss it as we go and at the end of syntax we have our wear and order by clause note here if you are using order by Clause then it must be the last statement of the QD before jumping over SQL Developer and start writing the cery let's once again take a look at the second line of our syntax which is our join Clause so that I can clear the concept of source and Target table first of all the designation of source table depend on the type of outer joint you are working with here we are working with left outer joint thus the table which is on the left side of our joint Clause is the source table and the one on the right side of the joint close is the target table this thing will be the opposite if we were using right outer joint since we are working with the left outer joint condition hence all the records of all the columns from the left side table or the source table will be a part of our result whereas only those record from the right side table or the target table that satisfy the join condition will be the part of the result in other words only the records of the target table are checked against the join condition by SQL engine and the source table is taken as it is now let's jump over SQL Developer and do our first cury first let's check the structure of our EMP and D table here is the structure of our EMP table EMP table has three column EMP ID EMP name and EMP salary where EMP ID column is is a primary key now let's check the structure of D table here is the structure of D table it also has three column D ID D name and EMP ID here D ID is the primary key and EMP ID is a foreign key now let's write our first Cy this cury is really simple in the first line we are selecting employee name from EMP table and Department name from the D table in the second line we have our join Clause you can either write left outer joint or just write left joint both are permissible and have the same meaning as well as perform the same task means this outer is totally optional if you want you can delete it and simply write left join and our join condition here is in the third line and is fairly simple as we are comparing value of EMP ID column of EMP table with the value of EMP ID column in the D table let's execute the cury before that let's clear this output here is our result let's have an in-depth look of the result for better understanding of the left outer joint here as you can see these are EMP and D table where D is our source table and EMP is our Target table now let's come to our result table as you can can see all the column records from The Source table is returned in a result regardless of the Fulfillment of join condition as for the Target table you can see here that only those record that fulfill the joint condition are being returned for example EMP ID one of the target table match the EMP ID one of the source table to return the corresponding EMP name Steve similarly EMP ID to match return return NY as the result and so on since the last record do not fulfill the join condition hence null is returned this means that we will be having all the records of D name column in a result set but only those record from EMP name column which will satisfy the join condition hope things are clear now let's jump ahead and try the same cury with using Clause here in this cury we used on join condition and in expression we supplied column which share same name and same data type similarly we can use on joint condition with the column which doesn't have same name but same data type you can use any column in the onjoin condition until they have same data type name of the column doesn't matter here let's quickly modify this query let's use D ID column of D table and EMP ID of EMP table although both have different name yet they have same data type which is number to demonstrate this I'll select this cury and paste it here and let's clear this output panel and we will change this EMP ID from D ID now let's execute this query and here is our result now let's jump over using join condition let's clear this output panel and let's give a comment let's make some space so that you people can see okay for this once again we will require our first query where we use on join condition with EMP column of the both table here is the cury we use using join condition when column participating in join cury have same name and same data type here as you can see we are using EMP ID column of both the table since both have same name and same data type second when joint query satisfy the equ joint condition hope you remember that a joint query satisfy the equ join condition only when we compare columns in join condition using equal to comparison operator here as you can see we have used equal to operator for comparing the value of EMP ID column of EMP table and D table thus we can say this is an equ join cury in simple word when the column in join condition share same name and same data type and are compared only using equal to comparison operator and no other comparison operator such as greater than less than Etc then we can use using joint condition thus here we have to remove this on joint condition in order to use using joint condition as we can use only one joint condition at a time in a quy so here we will write our using join condition delete this on join condition and write in using join condition unlike on join condition we just have to supply the column name which is common in both the table which here is the EMP ID that's all we have to do let's execute here is our result as you can see the result of our query where we use on condition with the EMP ID column and this query is the same hope now the concept of outer join is clear this time I try to make this concept as easy as possible but still if you have doubts you can contact me for that you can either email me your doubt or comment below you can also connect with me on my social media all the contact information is in the description box hope you enjoyed watching please help me in reaching out to more people by sharing my videos on your social media like my video by clicking Thumbs Up Button this motivates me a lot to make more such videos you can also comment your feedback thanks we'll see you soon with another skl tutorial this is Manish