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.