SQL Join Scenarios :
I have already given the SQL Joins explanation with multiple examples. In this article I will explain different SQL Join scenarios used in real life. SQL join is most important feature of any database language. The SQL joins connect two or more tables to achieve the specific functionality. I will try to give you best SQL Joins Examples in this article. The SQL Join scenarios are most important used in real industry.
Before starting with SQL Joins Examples i would like to give you the different examples :
1.Inner Join
2.Equi Join
3.Non-Equi join
4.Self Join
5.Left Outer Join
Inner Join Examples :
I will start with inner joins examples. So I will just give the multiple examples of inner join in this section. If you want to see the detailed examples and four different ways to write inner join you can check here.In this section i would like to give you definition of Inner join,its real life use and base syntax of inner join followed by example.
Definition of Inner join :
When two or more tables connected with each other with specified condition to fetch common records is inner join.
Syntax of Inner Join :
Select Column_name1,Column_name2…Column_name’n’ from Table T1,Table1 T2
Where T1.Column_name=T2.Column_name;
Example of Inner Join :
I would like to give you the inner join real life examples.If user want to fetch the Employee_name and Department_name from Employee table and department table where Emp_no is the joining condition.Following are different examples of inner join:
Type 1 : Using Where Clause
Select E.Employee_name,D.Department_name
From Employee E,Department D
Where E.Emp_no=D.Emp_no;
Type 2 : Using Join Clause
Select E.Employee_name,D.Department_name from
Employee E Join Department D —Join Clause
on E.Emp_no=D.Emp_no; —On Condition
Type 3: Using Natural Join
Select E.Employee_name,D.Department_name,E.Salary from
Employee E Natural Join Department D; —natural join clause
Type 4:
Select E.Employee_name,D.Department_name,E.Salary from
Employee E Join Department D —join
Using(Emp_no); —using condition
These are above four types of inner join using which user can implement the specific functionality.
SQL Join Scenarios for Equi join :
The Equi join is nothing but inner join with using equal to operator.
Definition of Equi Join:
Equi join is join using which one can break the Cartesian product of two or more tables with using specific condition.
Example :
Select E.Employee_name,D.Department_name
From Employee E,Department D
Where E.Emp_no=D.Emp_no;
SQL Join Scenarios for Non Equi join :
There are some real life situations where user needs to use non equi joins. I would like to give you the scenarios of non equi join in this section.
Definition of Non Equi Join:
If user wants to use other than equal to condition in joining two tables is known as non-equi join.The condition should be > < <> != .
Example :
If User want to fetch the Employees Name and Department name where Employee is not assign to any department or there is mismatch between Emp_no in Employee table and Department table.
Select E.Employee_name,D.Department_name
From Employee E,Department D
Where E.Emp_no<>D.Emp_no;
SQL Join Scenarios for Self join :
Self join is nothing but joining the table by itself.Sometimes there are so many situations where user needs to connect that table with itself with different joining conditions.This is called as self join.
Definition of Self Join :
1.Self join is nothing but joining the table with itself.
2.User can join the multiple instances of same table together to complete the specific requirement.
3.Self-joins are used to compare values in a column with other values in the same column in the same table.
4.The Self join is used to obtain the running count and running totals.
Syntax Of Self Join :
Select Column_name1,Column_name2….Column_name ‘n’ from Table A,Table B
Where A.Column_name = B.Column_name;
Example for Self Join :
The self join is used to find out the employee and his manager name in same employee table.
Select E.Employee_id,E.Name as ‘Employee Name’,F.Name as ‘Manager Name’ from Employee E,Employee F where E.Emp_jd=F.Mgr_id;
Left Outer Join Example :
Left outer join is nothing but retrieving the all records from left table and common records from both tables.In reporting like scenarios there are so many times user needs to fetch all records from left table and common records from right table. These kind of scenarios the left outer join is used.
Definition of Left Outer Join :
When user wants all the records from Left table (First table) and only equal or matching records from second table then Left outer join is useful.The unmatched records are considered as null records.
Syntax of Left Outer Join :
Type 1:Left Outer Join Syntax with + operator :
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1,table2 t2
where t1.col=t2.col(+);
Type 2:With Keyword :
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1 Left Outer join table2 t2
on t1.col=t2.col;
Example of Left Outer Join :
Using Type 1:
Select a.Employee_name,b.Department_Name from
Employee a,Department b
where a.Department_ID=b.Department_ID(+);
Using Type 2:
Select a.Employee_name,b.Department_Name from
Employee a left outer join Department b
on.Department_ID=b.Department_ID;
Right Outer Join Example :
Left outer join is nothing but retrieving the all records from left table and common records from both tables.In reporting like scenarios there are so many times user needs to fetch all records from left table and common records from right table. These kind of scenarios the left outer join is used.
Definition of Right Outer Join :
When user wants all the records from Right table (Second table) and only equal or matching records from First or left table then Right outer join is useful.The unmatched records are considered as null records.
Syntax :
Type 1:Right Outer Join Syntax with +
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1,table2 t2
where t1.col(+)=t2.col;
Type 2:With Keyword
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1 Right Outer join table2 t2
on t1.col=t2.col;
Example of Right Outer Join :
Using Type 1:
Select b.Department_Name,a.Employee_name from
Employee a,Department b
where a.Department_ID(+)=b.Department_ID;
Using Type 2:
Select b.Department_Name,a.Employee_name from
Employee a right outer join Department b
on.Department_ID=b.Department_ID;
Full Outer Join :
Definition of Full Outer Join :
1.The Full Outer Join and Cartisian joins are less used joins in day to day applications,reporting,web applications.
2.These joins are less used because it deals with all the data from left table as well as right table.So if we want all the records from both left and right table we will directly use table no need to use the joins.
Syntax of Full Outer Join :
Select t1.col1,t2.col2….t ‘n’col ‘n.’.
from table1 t1 full join table2 t2
on t1.col=t2.col;
Example of Full Outer Join :
Select a.Department_ID,b.Employee_Name from
Employee b full outer join Department a
on a.Department_ID=b.Department_ID;
These are above best SQL joins example with its type. I have tried to explain each and every type of join with SQL joins example which are useful in day-to-day industry work. Hope you like this article on SQL joins example. If you like this article or if you have any suggestions with SQL joins example kindly comment in to comment section.