SQL tutorial 27: Right Outer Join in SQL by Manish Sharma for RebellionRider

Key Takeaways

what's up internet this is Manish from rebellion.com and today in this SQL tutorial we will see what right outer joint is as the name suggest right outer joint is a form of outer joint that returns each and every record from The Source table and returns only those value from the target table that fulfill the join condition since we are talking about right outter joint hence Source table is the one situated on the right side of the right outer joint Clause where the target table is the one on the left side of this clause so let's jump over the SQL Developer and start with the tutorial to make this concept easy to understand let's quickly create two tables the name of our first table will be EMP and it will be having three columns by the name of EMP ID EMP name and EMP salary Here Comes EMP ID will be the primary key let's create now we have our EMP table let's create a second table the name of our second table will be D and this table will consist three columns D ID D name and EMP ID here DP ID will be the primary key and the column EMP ID will be the foreign key reference from our first table EMP let's create now we have created both our table let me insert some data into these table so that we can use them to understand this concept okay I have inserted the data now let me show you the data in both these table we have five rows in EMP table now data in the D table here as you can see we have five department sales account Finance it and marketing and corresponding to each department we have the employee ID if you remember EMP ID is a foreign key constant here sales department 1 has employee ID 1 meaning employee with employee ID one which is Steve work in sales department similarly employee with employee ID 2 work in account and employee ID 3 work in finance department but none of the employee work in it and marketing department as we haven't assigned any employee ID here now let's start with the queries so table is created data is inserted now we are all set and good to go so let's do our first query of write out a join our first query will be the simplest one and here we will see how to use right outter joint with on claw but before that let me clear this output panel and make some space here so that you people can see what's going on here in this cery we are selecting EMP name from EMP table and D name from D table and as you can see here table EMP is on the right hand side of our right outer joint Clause thus it's a source table and table DD is on the left hand side of right outer join Clause thus it's a Target table and next is our join condition basically there are two join condition on and using we will see how to use right outo join with using join condition in our next query but for this one we will concentrate over on join condition as it's on join condition then it become mandate for us to compare columns from both source and Target table now when should we use on joint condition in SQL joins you ask first when column which are participating in on jooin condition have different name and same data type or when columns which are participating in on join condition have same name and same data type so let's execute the cery and check out the result here is our result as you can see in this result all the records from EMP table which is our source table are returned but only those column from Target table D are returned which satisfy the expression in onjoin condition now quy 2 but before that let me clear this output panel in our last query we saw how to use right outer joint with on joint condition now we will see how to use right outer joint with using clause for that we will use the same previous cery that's why we will copy this cury and paste it right here and then after we will modify it so here is our cery now we typically apply using Clause with joints in SQL under two circumstances first when we are performing equ joint this means that whenever we use equal to comparison operator for comparing columns over which we want to put join secondly using Clause is also used when the name and data type of column over which you want to put join in your cury is same to read more about equ join you can read SQL join article on my website the link for that is in the description box below when both these condition are satisfied then we can use using Clause let's use it as both on and using Clause are join condition we can use only one at a time and not together together in using Clause we just have to supply name of a column over which we want to emphasize join let's execute this query here is our result which is the same as previous cury now let's check out our third cury we use wear Clause when we want to filter the result written by our Cy in the previous cury we were extracting employees name with the Department name in which they were working say now you want to extract only those employees who have salary greater than 50,000 for this our cury will be the same as previous but along with an additional wear Clause as our query will be the same as previous so we are going to copy this query and paste it here and now we will modify it accordingly we just have to put wear Clause after the using or on clause which is our joint condition you just simply have to write where and your expression here this is our expression now let's execute here is our result that is all on right outer joint for now keep watching for more interesting videos also kindly subscribe share and like thanks for watching this is Manish from Rebellion Rider