SQL Inner Join Example :

I have already written article on SQL joins with multiple examples.I would like to explain SQL Inner Join Example in depth in this article.The article will give you multiple ways to write SQL Inner Join.There are four different ways to write Inner join in SQL.I would like to give you different SQL Inner Join Example.I will explain the SQL inner join example with considering the performance tuning factors and finally provide you best way to write SQL inner join.

Definition of Inner join :

1.When two or more tables connected with each other with specified condition to fetch common records is inner join.

2.Inner join is nothing but fetching the common records from one or more tables with specific condition.

3.Inner join is most important and most used join in real world scenarios.

4.In this article i would like to explain four different syntax of inner join with real life examples so that you will get the idea about inner join.

Inner Join Type 1 : Using Simple Where Condition

The First type of join filters the Cartesian product of two or more tables according to the condition.The where condition is really very important here.There are so many times when user forgets to write the where condition while using this join.

Syntax :

Select Column1,Column2….Column n From Table1 A,Table2 B

Where A.Condition_Column=B.Condition_Column;

The above syntax is used to join the Table1 and Table 2 with where condition. This is the best solution according to the performance tuning.

Example : 

Let us consider that there are two tables Employee and Department where the Employee_No is primary key which is foreign key in Department table.If user wants to fetch the Employee_name,Salary from Employee table and Department_name from Department table then following query is useful.

Select E.Employee_name,D.Department_name,E.Salary from

Employee E,Department D                      —Cartesian product

where E.Employee_no=D.Employee_no;   —Joining condition

The above example gives you the Employee as well as department data with using the Employee_no as joining condition.

Inner Join Type 2 : Using Join Clause in the Query

The Join clause is used to join two or more tables in spite of using the filtering of Cartesian product.The Join clause is implemented for user friendliness. We are joining two tables so in spite of using the Cartesian product and then where condition the direct join clause is implemented in oracle.The Join clause by default indicates the inner join of two tables.

Join Clause:

1.The join clause is clause which exactly joins two tables or views.

2.The On clause is used to specify the joining condition between two tables.

3.User can use where condition after using on conditions.

4.If there are multiple tables user can use the ‘and’ And ‘Or’ conditions.

Syntax :

Select Column1,Column2….Column n From Table1 A Join Table2 B

on A.Condition_Column=B.Condition_Column

Where Condition;

Example :

Lets consider the example of two tables. If user wants to fetch the Employee_name,Salary from Employee table and Department_name from department table where the Designation is ‘IT Specialist’.With using join clause user can write the query

Select E.Employee_name,D.Department_name,E.Salary from

Employee E Join Department D                      —Join Clause

on  E.Employee_no=D.Employee_no               —On Condition

Where E.Designation=’IT Specialist’;               —Where condition

The above query will fetch the Employee data as well as department data where designation is ‘IT Specialist’.The above way is another way to write the joins.There are other clauses to join two or more tables.

Different Join Clauses :

Left Outer Join Clause to write left outer join

Right Outer Join Clause to write right outer join

Full Outer Join Clause to write full outer join

Inner Join Type 3 : Inner join with ‘Using’ Clause

The third type of join which is used to reduce the code is with ‘Using’ Clause.The USING clause specifies which columns to test for equality when two tables are joined.If the columns from two different tables are with same name as we are taking the Employee_no as joining condition in above example.Then to shorten the code user can use the ‘Using’ clause.

Syntax :

 Select Column1,Column2….Column n From Table1 A JoinTable2 B

Using(Column_name1,…Column_name..n);

The above syntax is used to join two or more tables with ‘Using’ Clause. The Using clause is used to shorten the specified code.In real world scenarios user needs to write huge complex sql queries.In those cases user needs to shorten the code and with ‘Using’ clause user can shorten the code.

Example :

Let us consider that there are two tables Employee and Department where the Employee_No is primary key which is foreign key in Department table.If user wants to fetch the Employee_name,Salary from Employee table and Department_name from Department table then following query is useful with ‘Using’ clause.

Select E.Employee_name,D.Department_name,E.Salary from

Employee E Join Department D                      —join

Using(Employee_no);                                —using condition

The above example uses ‘Using’ clause with the joining condition named ‘Employee_no’

The main use of ‘Using’ Clause is to shorten the code.

Inner Join Type 4 : Inner join with ‘Natural Join‘ Clause

The fourth type of implementing the inner join is using ‘natural join’ clause.The ANSI standard provides another option to write less code if we want to join our tables using all of the columns that have the same name in both sources.The only condition to use the natural join clause is the column_name of joining condition should be same in both the tables you are joining.

Syntax :

 Select Column1,Column2….Column n From Table1 A Natural JoinTable2 B

Where Condition;

Example :

I want to take same example of Employee table and department table where user need to fetch the data and the condition of the same is ‘Employee_No’.You need to fetch data where designation is ‘IT Specialist’.

Select E.Employee_name,D.Department_name,E.Salary from

Employee E Natural Join Department D                      —natural join clause

where E.Designation=’IT Specialist’;

 

The basic use of natural join clause is to shorten the code. User needs to use the Natural join clause very carefully because we are not specifying the condition while using natural join. If user is confirmed about the relation between two tables then and only then the natural join clause needs to be used.

The best inner join syntax by considering the performance tuning is with using where condition.(Type 1)

I hope you like this article on SQL Inner Join Example.If you like this article of SQL Inner Join Example or if you have any suggestions with the same kindly comment in comment section.