An Introduction to SQL Joins

There are various kind of joins available in SQL. Joins means, joining the data of multiple tables based on some given criteria in a way, that we can get the related data from multiple tables by one query only. However think that how these joins work. Ultimately joins also need to fetch the data from database storage system itself. So how database system manages it. Here is a brief overview for it. 

• Database System identifies the queries which will retrieve the base data, and may have conditional clause.  
• It executes those query by using its existing query (and condition) mechanism.
• It also uses index here, if these are available for corresponding table and columns. 
• This way, database system filter the amount of data to next smaller level (as much as possible). After this process, now database system has a smaller set of data where it needs to find the required data. 
• Now Database System need to apply various optimized logic to iterate over the rest of the data and to filter these to have desired set of data. This process, of course, could be very heavy as we need to traverse over a large amount of data and need to find the right combination in other table data. This is the reason that every database system tries to have best optimized execution plan so that every base query can be executed first with required condition. This will enable it to have smallest possible set of data for further operation. So having an best optimized execution plan is the major feature of any database system, as it will affect the speed of all the queries.  
• It also indicates that we should index the data which is frequently used in queries (as conditions), so that database system can use it to filter the data.