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. 

Here is brief description of various popular joins:

• Inner Join - It is used to contain information from a combination of two or more tables based on specified condition in 'where' clause. It returns all rows from both tables which matches with the given condition. If there are rows in first table which do not have matches in second tables, those rows will not be returned. Special care must  be taken when joining tables on columns that can be Null, since 'Null' will never match with any other 'Null' value, unless the  join condition uses explicitly the 'IS  NULL'  or  'IS NOT NULL' predicates. Syntax: select f1, f2, f3 from a inner join b on a.f1 = b.f1. Here are various types of inner joins: 

1. Explicit Inner Join - In this, we explicitly specify the 'INNER JOIN' key word while joining the tables. 
1. Syntax: select * from employee INNER JOIN department ON employee.departmentID = department.DepartmentID 
2. Implicit Inner Join - Where we don't specify the 'INNER JOIN' key word but a simple comma represents this intention. 
1. Syntax: select * from employee, department where employee.DepartmentId = department.DepartmentID 
3. Equi Join - An equi join is a type of theta join that  uses only equality comparisons in the join predicate. Using other comparison operators like < distinguish a join from equi-join. 
1. Syntax: select * from employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID 
4. Natural Join - A natural join is a further specialization of equi-joins. The join predicate is implicitly given by comparing all columns in both tables that have the same column name in the tables to be joined. The resulting joined  table contains only one column for each pair of equally-named columns. 
1. Syntax: select * from employee NATURAL JOIN department 
5. Cross Join: A cross Join or Cartesian Join is the foundation upon which all types of inner joins are built. A cross join returns the Cartesian product of the sets  of records from the two joined tables. Thus, it is an inner  join where the join condition always evaluates to true. If A and B are two sets then cross join = A X B. The SQL code for a cross join lists the tables  to be joined, but does  not include any filtering join predicate. 
1. Syntax of explicit Cross Join: select * from employee CROSS JOIN department 
2. Syntax of implicit Cross Join: select * from employee, department 

• Outer Joins - Outer joins do not require that each record  in the two joined  tables  has a matching record in the other table. The record is retained in the joined  table if  no matching record exists in other table. Outer joins are subdivided further  into left outer joins, right outer joins and full outer joins based on from which table the rows shall be retained. There is no explicit join notation for outer joins. 

1. Left Outer Join - The result of a left outer join for tables A and B always contains all records from 'left' table A, even if the join conditions does not find any matching record in the 'right' table B. This means that if the ON clause matches zero records in B, all rows of table A will be return with all columns of B table but with NULL value in each column from B. 
1. Syntax: select distinct * from employee LEFT OUTER JOIN  department ON employee.DepartmentID = department.DepartmentID 
2. Right outer join - It is much like a left  outer join except that tables are reversed. A right outer join returns all the values from right table and matched values from left table (or null in case of no matching join predicate). 
1. Syntax: select distinct * from employee RIGHT OUTER JOIN  department ON employee.DepartmentID = department.DepartmentID 
3. Full Outer Join - It combines the results of both left and right outer join. The joined  tables will contain all record from both tables, and NULL for missing match on either side. 
1. Syntax: select distinct * from employee FULL OUTER JOIN  department ON employee.DepartmentID = department.DepartmentID 
2. Some databases do not support FULL OUTER JOIN, but it can be simulated using union between left and right outer joins. 

People who read this post also read :