SQL tutorial 24 :SQLJoins- Natural Join With ON and USING clause By Manish/Rebellionrider

Key Takeaways

what's up internet this is Manish from Rebellion rider.com and today in this SQL orle database tutorial we will learn about natural joint first why do we have joints in SQL the answer is that when you want to retrieve information from two or more tables which share common field we use joins there are several kinds of join in SQL and I'll be explaining to you all of them one video at a time and today we will start with natural join but before jumping over a natural joint let me tell you a few terminologies First Source table table which comes after from clause in the select statement second target table all the tables which come after join clause in the query when the joint cury is executed then Oracle engine start matching data from Source table to the Target table if there is a hit for a matching Source table data in the Target table then the value is returned okay let's jump over SQL Developer scenario one when there is only one identical name column between source and Target table let's use Department table and location table for the demonstration as you can see here Department table has four columns Department ID Department name manager ID and location ID similarly location table has six column location ID street address postal code city state Province and Country ID upon minutely observing you will notice that location ID is the only common column between our source and Target table it is the foreign key and thus has the same data type and column width let's write the cury for natural joint say you want to find the city for all your departments in this Cy we are selecting Department name from Department's table and City from locations table you can select other columns too department is our source table as it's coming right after from key word and location is a target table as it's coming after join Clause the best part of using natural join is that you do not need to specify the join columns because column with same name in source and Target table are Auto aut atically associated with each other let's execute the cury here is our result it department is in the South Lake City shipping is in the South San Francisco and so many departments are in Seattle City on executing this cury Oracle returns all the rows from Department column of departments table and from City column of location table where location ID of Department's table is equal to location ID of location table now scenario two what if when our source and Target table have more than one identical name column let me clear the output for you and let me make some space to demonstrate this scenario I'll be using employees table and Department's table here as you can see there are two columns Department ID and manager ID which are common in both the table so if you put natural join on these two table then Oracle engine will use these two column to return the result don't get confused let's do a quy and try to understand what does that statement means say we want to see the name of employees and the name of Department in which he or she is working for that we will select first name from employees table which is are Source table and Department name from Department table execute here is our result as I have already mentioned that when we use natural joint there is no need to specify joint columns explicitly all the columns which are common in both source and Target table get Associated automatically the cury in which all the common Columns of source and Target table get Associated automatically are known as pure natural joint which mean in this cury Oracle has automatically Associated our common column which are Department ID and manager ID to help you understand more clearly let's write same cury but this time using on Clause here is a slightly change in natural join syntax as we are using on Clause this time instead of writing natural join we will only write join therefore now we have our on clause in our above cury which was pure natural join Oracle is using both the common Columns of our source and Target table which are manager ID and Department ID thus we will also use both these column in our own clause both this cery produce the same result as both are same query written in different way let's execute as you can see there is no difference between the result of previous cery and the latest cury in this cury we emphasize natural joint using on Clause as we are using on Clause thus it become mandatory to specify the column over which we want to perform the joint now scenario three natural joint with using Clause here once again we will use our natural joint cury but before that let me clear the output here so that you can see things clearly okay to make you understand this concept clearly one again we need our Pure natural joint cery and this one is our Pure natural joint cery to save the time I'm going to select copy and paste this cury sorry as we can see here in this cery that when we have more than one common column Oracle engine uses all of them to produce the result now here in this cury we are using employees and Department table which have two common columns Department ID and manager ID what if you want to use only manager ID in your natural join instead of both manager and Department ID in this situation we use natural join with using Clause say we want to select all the first name and Department name from Source table employees and Target table departments from where we have equal values of manager ID in both employees table and the department table let's write the cury execute and here is our result hope it's clear why we use using Clause with natural join so guys that's all for today on natural join if you want to read about this concept you can visit my website and if you want to download this script just scroll down a bit and uh here is the link for this script keep watching for More Concept on various other types of join also please share this video with your friends as well as don't forget to give me thumbs up if you like this video moreover don't forget to hit that subscribe button see you soon till then take care have a good time this is Manish