How to use INNER JOIN in SQL

In this article, we will learn about the INNER JOIN concept and how to use it in SQL with the WHERE clause.

What is INNER JOIN in SQL?

Inner Join is a type of join in the SQL. Inner Join in SQL is a widely used join. This join returns only those rows which are common in both tables. Inner join is used to join two tables.

Syntax of Inner Join in the SQL:

SELECT table1.columname1, table1.columnname2, table2.columnname1, table2.columnname2 FROM TABLE1 INNER JOIN TABLE2 ON table1.column = table2.column;

Table1.column = table2.column is the common column that maintains the parent child relationship between these two tables.

There are some steps that we have to use in the Inner Join in the SQL query:

1. Create a new database or use an existing database by selecting the database using the USE keyword followed by the database name.

2. Create a new table inside the selected database, or you can use an already created table.

3. If the table is newly created, insert the records in the newly created database using the INSERT query.

4. View the inserted data using the SELECT query without the Inner Join query.

5 Now, we are ready to use the Inner Join in the SQL queries.

Step 1: Create a new database or use an already created database.

I have already created a database. I will use my existing created database name, Company.

USE Company;

Company is the database name.

Those who didn’t have created a database follow the below query to create the database:

CREATE DATABASE database_name;

After creating the database, select the database using the USE keyword followed by the database name.

Step 2: Create a new table or use already existing table:

I have already created a table. I will use the existing table named Employees and Manager as we are executing the Inner Join query.

To create the new table, follow the below CREATE TABLE syntax:

CREATE TABLE table_name(

columnname1 datatype(column size),

columnname2 datatype(column size),

columnname3 datatype(column size)

);

Step 3: Insert the records in the newly created table using the INSERT query

Use the below syntax to insert new records in the table:

INSERT INTO table_name VALUES(value1, value2, value3);

Step 4: View the records using the SELECT query.

View the records from the table using the following syntax:

SELECT * FROM table_name;

The following query will display the records of Employees.

SELECT * FROM Employees;

The output of the above SELECT query is:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
6001	RAHUL	NIKAM	54500	BANGALORE	TESTING	4
6002	ATISH	JADHAV	60500	BANGALORE	C#	5
6003	NIKITA	INGALE	65000	HYDERABAD	ORACLE	1

The following query will display the records of Manager.

SELECT * FROM Manager;

The output of the above SELECT query is:

Managerid	manager_name	manager_department
1	Snehdeep Kaur	ORACLE
2	Kirti Kirtane	FMW
3	Abhishek Manish	JAVA
4	Anupam Mishra	TESTING
5	Akash Kadam	C#

The following query will display the records of Laptop.

SELECT * FROM Laptop;

The output of the above SELECT query is:

LAPTOPID	NAME	EMPLOYEEID
101	DELL	NULL
102	HP	1002
103	LENOVO	NULL
104	HP	3003
105	DELL	4002
106	LENOVO	4003
107	DELL	5001
108	HP	NULL
109	DELL	NULL
110	HP	NULL
111	LENOVO	2002
112	LENOVO	6003
113	HP	1003

Step 5: We are ready to use the INNER JOIN in the queries

Let's understand the Inner Join with the help of examples.

Example 1: Write a query to display employee id, first name, last name, salary, city from the employee's table, and manager id and manager name from the manager table using the Inner join.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.SALARY, E.CITY, M.MANAGERID, M.MANAGER_NAME FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID =M.MANAGERID;

In the above query, we have fetched employee id, first name, last name, salary, city from the employee's table and manager id, manager name from manager table where employees’ table manager id column equals to manager table manager id column. The query will return all the matches records from both tables. Manager id is a common column between both tables. E is an alias name for the employee's table, while M is for the manager table. The manager id column works as a foreign key in the employee's table. The manager id works as the primary key in the manager table, which creates a parent-child relationship between the two tables.

The output of the above query is:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
6003	NIKITA	INGALE	65000	HYDERABAD	ORACLE	1
1003	NIKHIL	VANI	50500	JAIPUR	FMW	2
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
4002	ASHWINI	BAGHAT	54500	NOIDA	JAVA	3
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
5001	ARCHIT	SHARMA	55500	DELHI	TESTING	4
6001	RAHUL	NIKAM	54500	BANGALORE	TESTING	4
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
6002	ATISH	JADHAV	60500	BANGALORE	C#	5

The records are displayed in the ascending order by default manager id.

Example 2: Write a query to display employee id, first name, last name, city, and department from the employee's table and manager id and manager name from manager table using Inner Join where employee department is C#.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.LAST_NAME, E.CITY, E.DEPARTMENT, M.MANAGERID, M.MANAGER_NAME FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID =M.MANAGERID WHERE DEPARTMENT = ‘C#’;

In the above query, we have fetched the employee id, first name, last name, city, and department from the employee's table, manager id, and manager name from manager table where manager id from employees table equal to manager id from manager table only those employee records whose employee department is C#.

The output of the above query is:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
2003	RUCHIKA	JAIN	50000	MUMBAI	C#	5
5003	ROSHAN	NEHTE	48500	CHANDIGARH	C#	5
6002	ATISH	JADHAV	60500	BANGALORE	C#	5

In the obove output, we can see only those records are retrieved from employees whose department is C#. We use the WHERE clause with the INNER join.

Example 3: Write a query to display the employee id, first name, salary, city, and department from employee’s table, and laptop id and laptop name from laptop table using Inner Join.

SELECT E.EMPLOYEEID, E.FIRST_NAME, E.SALARY, E.CITY, E.DEPARTMENT, L.LAPTOPID, L.NAME FROM EMPLOYEES E INNER JOIN LAPTOP L ON E.EMPLOYEEID = L.EMPLOYEEID;

In the above query, we have fetched the employee id, first name, salary, the city, and department from employees’ table, and laptop id, and name from laptop table where employee id from employee table equals to the employee id from laptop table. Employee id is a foreign key in the Laptop table, which creates a parent-child relationship between the Employee table and Laptop table.

The output of the above query is:

EMPLOYEEID	FIRST_NAME	SALARY	CITY	DEPARTMENT	LAPTOPID	NAME
1002	VAIBHAV	60000	NOIDA	C#	102	HP
3003	DEEPAM	58500	MUMBAI	JAVA	104	HP
4002	ASHWINI	54500	NOIDA	JAVA	105	DELL
4003	RUCHIKA	60000	DELHI	ORACLE	106	LENOVO
5001	ARCHIT	55500	DELHI	TESTING	107	DELL
2002	BHAVESH	65500	PUNE	FMW	111	LENOVO
6003	NIKITA	65000	HYDERABAD	ORACLE	112	LENOVO
1003	NIKHIL	50500	JAIPUR	FMW	113	HP

Only those records are displayed in the given output to whom the Laptop is assigned.

Example 4: Write a query to display employee id, first name, salary, and department from employees table, and manager Id and manager name from manager table using inner join where salary > 57000 or department is Oracle.

SELECT EMPLOYEEID, FIRST_NAME, SALARY, DEPARTMENT, M.MANAGERID, M.MANAGER_NAME FROM EMPLOYEES E INNER JOIN MANAGER M ON E.MANAGERID = M.MANAGERID WHERE SALARY > 57000 OR DEPARTMENT = 'ORACLE';

In the above query, we have fetched the employee id, first name, salary, and department from the employee's table, and  manager id and manager name from manager table where manager id from employees table equals to the manager id from manager only those employees whose salary is greater than 57000 or Department is Oracle.

The output of the above query is:

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID
1001	VAIBHAVI	MISHRA	65500	PUNE	ORACLE	1
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1
4003	RUCHIKA	AGARWAL	60000	DELHI	ORACLE	1
6003	NIKITA	INGALE	65000	HYDERABAD	ORACLE	1
2002	BHAVESH	JAIN	65500	PUNE	FMW	2
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3
5002	SANKET	CHAUHAN	70000	HYDERABAD	JAVA	3
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4
1002	VAIBHAV	SHARMA	60000	NOIDA	C#	5
6002	ATISH	JADHAV	60500	BANGALORE	C#	5

In the above output, only those employee records are displayed whose salary is greater than 57000 or employee department is 'Oracle'. The OR operator display records if one of the given condition in the query is true.