Different Types of Joins in SQL

                          Different Types of Joins in SQL

Joins are classified into three types as below:

1. Inner Join
2. Outer Join
3. Cross Join

We will look into this by considering an example. Suppose we have two table as follows:

Empdetails:

EmpSalary:

Inner and outer joins perform join on the basis of at least one field. in our example this common file is empid.

• Inner Join: Inner join is the most commonly used join. This join returns rows when there is a match in both the tables. Now we will apply this join between the two sample tables

                     Query:

                                 select empdetails.empid,empdetails.empname,empsalary.empsalary

                                 from empdetails , empsalary where empdetails.empid = empsalary.empid

                 Now in this query we are trying to pull salary of employee from empsalary tables for the                                employees which are present in empdetails table The result for this query will be as follows:

• Outer Join: Outer join is of three type: Left outer Join and Right Outer Join

Left outer Join: This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

Example:

Query:

                     select empdetails.empid,empdetails.empname,empsalary.empsalary
                     from empdetails left outer join empsalary on empdetails.empid = empsalary.empid

Output:

Right outer Join:This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

Example:

Query:

    select empdetails.empid,empdetails.empname,empsalary.empid,empsalary.empname,empsalary.empsalary

    from empdetails right outer join empsalary on empdetails.empid = empsalary.empid

Output:

Full outer Join:This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

Example:

Query:

select empdetails.empid,empdetails.empname,empsalary.empid,empsalary.empname,empsalary.empsalary

from empdetails full outer join empsalary on empdetails.empid = empsalary.empid

Output:

• Cross Join: It is the Cartesian product of the two tables involved. 

Example:

Query:

   select empdetails.empid,empdetails.empname,empsalary.empid,empsalary.empname,empsalary.empsalary

   from empdetails cross join empsalary 

Output:

Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the join conditions.