SQL Inner Join

In Structured Query Language, the most used join query is the Inner join query.

Inner join query retrieves the records from one or more tables with similar data or records. The joins condition returns only common rows between the tables.

Inner Join is one of the types of joins in the Structured Query Language Join.

Suppose we have two tables, Table A and Table B. Table A have some columns, and Table B also has some columns. How joining will happen between these two-columns? In this case, there is one common column between these two tables. So joining will be met through this common column.

Syntax of Inner Join in SQL:

SELECT COLUMNNAME1, COLUMNNAME2, COLUMNNAME3, COLUMNNAME4 FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.COLUMN NAME = TABLE2.COLUMN NAME;

Example of Inner Join in Structured Query Language:

Consider these tables to understand the Inner Join concept.

Table 1: Employees:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	BANGALORE	C#	4
1003	NIKHIL	VANI	50500	HYDERABAD	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	4
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	HYDERABAD	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	5
4002	ASHWINI	BAGHAT	54500	BANGALORE	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	5
5002	RAKESH	KUMAR	70000	CHANDIGARH	C#	4
5003	MANISH	SHARMA	62500	BANGALORE	TESTING	5

Table 2: Manager.

Manager_Id	Manager_Name	Manager_Department
1	Kirti Chaudhary	ORACLE
2	Manish Bafna	FMW
3	Snehdeep Kaur	JAVA
4	Satish Kumar	C#
5	Anupam Mishra	TESTING

Example 1: Execute an inner join query on the employee and manager table name.

SELECT Employee_Id, First_Name, Last_Name, Salary, City, Department, M.Manager_Id, Manager_Name FROM Employees E INNER JOIN Manager M ON E.Manager_Id = M.Manager_Id;

The above Inner join query joins the employees' table and manager table and retrieves the data from the tables where E.Manager_Id = M.Manager_Id.

The output of the above query is as follows:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGER_ID	MANAGER_NAME
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1	Kirti Chaudhary
1002	VAIBHAV	SHARMA	60000	BANGALORE	C#	4	Satish Kumar
1003	NIKHIL	VANI	50500	HYDERABAD	FMW	2	Manish Bafna
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1	Kirti Chaudhary
2002	BHAVESH	JAIN	65500	PUNE	FMW	2	Manish Bafna
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	4	Satish Kumar
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3	Snehdeep Kaur
3002	ANUJA	WANRE	50500	HYDERABAD	FMW	2	Manish Bafna
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3	Snehdeep Kaur
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	5	Anupam Mishra
4002	ASHWINI	BAGHAT	54500	BANGALORE	JAVA	3	Snehdeep Kaur
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1	Kirti Chaudhary
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	5	Anupam Mishra
5002	RAKESH	KUMAR	70000	CHANDIGARH	C#	4	Satish Kumar
5003	MANISH	SHARMA	62500	BANGALORE	TESTING	5	Anupam Mishra

Example 2: Execute an inner join query on table name Employees and Manager using order by clause.

SELECT Employee_Id, First_Name, Last_Name, Salary, City, Department, M.Manager_Id, Manager_Name FROM Employees E INNER JOIN Manager M ON E.Manager_Id = M.Manager_Id ORDER BY Manager_Id;

The above inner join query joins the two tables named Employees Table and Manager Table and retrieves the data from both the tables where E.MANAGER_ID = M.MANAGER_ID. The employees' records will be displayed in the ascending order by Manager_Id as we have used the ORDER BY clause in the query. The query will display only similar records between the two tables.

The output of the above query is as follows:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGER_ID	MANAGER_NAME
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1	Kirti Chaudhary
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1	Kirti Chaudhary
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1	Kirti Chaudhary
2002	BHAVESH	JAIN	65500	PUNE	FMW	2	Manish Bafna
3002	ANUJA	WANRE	50500	HYDERABAD	FMW	2	Manish Bafna
1003	NIKHIL	VANI	50500	HYDERABAD	FMW	2	Manish Bafna
4002	ASHWINI	BAGHAT	54500	BANGALORE	JAVA	3	Snehdeep Kaur
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3	Snehdeep Kaur
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3	Snehdeep Kaur
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	4	Satish Kumar
5002	RAKESH	KUMAR	70000	CHANDIGARH	C#	4	Satish Kumar
1002	VAIBHAV	SHARMA	60000	BANGALORE	C#	4	Satish Kumar
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	5	Anupam Mishra
5003	MANISH	SHARMA	62500	BANGALORE	TESTING	5	Anupam Mishra
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	5	Anupam Mishra

Example 3: Execute an inner join query on table name Employees and Manager using group by clause.

SELECT Employee_Id, First_Name, Last_Name, Salary, City, Department, M.Manager_Id, Manager_Name FROM Employees E INNER JOIN Manager M ON E.Manager_Id = M.Manager_Id GROUP By Salary;

The above inner join query joins the two Employees Table and Manager Table and retrieves the data from the tables where E.MANAGER_ID = M.MANAGER_ID. The data is displayed in the GROUP BY employee salary. Employees with the same salary will be in a group with one salary. Employees who don't have the same salary number as another employee's salary, will have separate salary group.

The output of the above query is as follows:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGER_ID	MANAGER_NAME
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	4	Satish Kumar
1003	NIKHIL	VANI	50500	HYDERABAD	FMW	2	Manish Bafna
4002	ASHWINI	BAGHAT	54500	BANGALORE	JAVA	3	Snehdeep Kaur
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1	Kirti Chaudhary
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3	Snehdeep Kaur
1002	VAIBHAV	SHARMA	60000	BANGALORE	C#	4	Satish Kumar
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	5	Anupam Mishra
5003	MANISH	SHARMA	62500	BANGALORE	TESTING	5	Anupam Mishra
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1	Kirti Chaudhary
5002	RAKESH	KUMAR	70000	CHANDIGARH	C#	4	Satish Kumar

Example 4: Execute an inner join query on table name Employees and Manager using where clause.

SELECT Employee_Id, First_Name, Last_Name, Salary, City, Department, M.Manager_Id, Manager_Name FROM Employees E INNER JOIN Manager M ON E.Manager_Id = M.Manager_Id WHERE Salary > 52000;

The above inner join query joins the two Employees Table and Manager Table and retrieves the data from the tables where E.MANAGER_ID = M.MANAGER_ID. The query will display only employee's data where the employee salary is greater than 52000.

The output of the above query is as follows:

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGER_ID	MANAGER_NAME
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1	Kirti Chaudhary
1002	VAIBHAV	SHARMA	60000	BANGALORE	C#	4	Satish Kumar
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1	Kirti Chaudhary
2002	BHAVESH	JAIN	65500	PUNE	FMW	2	Manish Bafna
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3	Snehdeep Kaur
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3	Snehdeep Kaur
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	5	Anupam Mishra
4002	ASHWINI	BAGHAT	54500	BANGALORE	JAVA	3	Snehdeep Kaur
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1	Kirti Chaudhary
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	5	Anupam Mishra
5002	RAKESH	KUMAR	70000	CHANDIGARH	C#	4	Satish Kumar
5003	MANISH	SHARMA	62500	BANGALORE	TESTING	5	Anupam Mishra