SQL tutorial 29: Full Outer Join with example By Manish Sharma/ RebellionRider

Key Takeaways

what's up internet I'm Manish from Rebellion rider.com and I'm back once again with another SQL tutorial but first of all a heartfelt thank to all my viewers and subscriber you people are really amazing since last two videos we are concentrating over outer joint and so far we have done right and left outer joint the only one which is left now is full outer joint yes you guessed correctly today's skill tutorial is about full outer joint before moving ahead just want to say in order to better understand the concept of full outro please watch my last two tutorial over right and left out to join for your convenience I'll put the link of both these tutorial in the description box below be happy and enjoy watching okay let's jump over SQL Developer and start the tutorial okay once again we will be using same table which we have used so far in this outer joint series these are EMP and D tables let's have a quick look of a structure and the data of these two TBL our table EMP has three column EMP ID EMP name and EMP salary here column EMP ID is a primary key table D also has three column D ID D name and EMP ID here in this table column D ID is a primary key where column EMP ID is a foreign key reference from the EMP table now let's have a look at the data of these two table EMP table has five rows where column one has EMP ID 1 1 2 3 4 and 5 and column two has EMP name Steve Nancy guu Alan and Julia and column three consist the salary of these employees now data of DP table table D also has five column column 1 hold D ID again 1 2 3 4 and five where column two has D name sales account Finance it and marketing and column three EMP ID which is the foreign key has only three records corresponding to Department name sales account and finance we can interpret this data easily for example the employee with employee ID 1 is named Steve in our EMP table and works in department of sales similarly employee with employee ID 2 is Nancy who works in account department and employee with employee ID 3 is Guru who works in finance also we have not assigned any employee ID for Department of it and marketing which signifies that no one works in these Department I think the table's structure and its data are clear now so now it's time to jump over our topic full outer joint full outer joint is kind of combination of both right outer joint and left after joint because it returns all the rows from left as well as right side table let's have a look of full after joint syntax syntax is pretty similar to a left or right outer joint as you can see we have our selected statement where you can specify the name of the columns from both the participating tables followed by from clause and our join clause which is full outer joint here you can either write full outer joint or just full join since both are permissible and perform the same task and then we have our join condition on and using followed by where and order by Clause okay now without wasting much time let's do our first query okay once again we are on our SQL Developer let's do our first cury yes we are going to perform full outer joint with on Clause let's write the cury here in this cury we are selecting EMP name column from EMP table and D name column from D table in our full outer joint Clause we have EMP table on left side and D table on right side and then we have our on Clause where we are comparing the value of EMP ID columns from both the tables let's execute if you will observe minutely then you can see that the result till row five is similar to that of right outer joint as all the records from right side table is here and only those record from left side table which satisfy the join condition are here in this result followed by all the remaining record from the table does the last row six and seven contain the remaining EMP name here in this on join Clause we used column which have same name and data type now let's use column which have different name and same data type for example column D ID let's copy this query and modify its on Clause let's execute here is our result perfect five column now let's check out full outer joint with using Clause let me clear this output panel we use using joint condition when the column in joint condition share the 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 so let's copy our first query and modify it as you can see in the join condition of this cury we have used EMP ID column of both the table this column share the same name and same data type hence we can easily replace this on Clause with using Clause let's do it now let's execute this query here is our result if you compare this result with the result of our first query then you will will find them as exactly the same you can also limit the result by using we Clause say you want to see the name of only those employees and their departments who have salary of less than 50,000 for that just add a wear Clause let's write another cury here so let's use this cery to demonstrate wear Clause copy this query and paste it let's clear this output panel let's add our wear Clause let's execute this query that's the result similarly you can sort the result using order by Clause say you want to sort this result in ascending order according to the employee name for that just add the order by Clause followed by the column name let's once again do our fourth query once again clear this output panel and this time we will select this cury oh sorry and we will use it here now here we will add the Clause order by and the column name will be EMP name if you execute this query then by default the result will be sorted in ascending order let's execute as you can see here is our result if you write DC right after the column your result will be sorted in descending order for example here just after the column name right DC descending now let's execute this cury as you can see our result is now sorted in descending order that's it for today's guys you can download this script from my website link is in the description box this is the last tutorial in outer join Series this means outer join topic is now complete hope you enjoyed watching if you like my video please give me thumbs up your each and every like motivates me a lot for doing more such videos if you have any query feel free to write and please help me in gaining more subscriber by sharing my video with your friends also don't forget to subscribe thanks we'll see you soon this is Manish