SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
1002VAIBHAVSHARMA60000NOIDAC#5
1003NIKHILVANI50500JAIPURFMW2
2001PRACHISHARMA55500CHANDIGARHORACLE1
2002BHAVESHJAIN65500PUNEFMW2
2003RUCHIKAJAIN50000MUMBAIC#5
3001PRANOTISHENDE55500PUNEJAVA3
3002ANUJAWANRE50500JAIPURFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
4002ASHWINIBAGHAT54500NOIDAJAVA3
4003RUCHIKAAGARWAL60000DELHIORACLE1
5001ARCHITSHARMA55500DELHITESTING4
5002SANKETCHAUHAN70000HYDERABADJAVA3
5003ROSHANNEHTE48500CHANDIGARHC#5
6001RAHULNIKAM54500BANGALORETESTING4
6002ATISHJADHAV60500BANGALOREC#5
6003NIKITAINGALE65000HYDERABADORACLE1

The following query will display the records of Manager.

SELECT * FROM Manager;

The output of the above SELECT query is:

Manageridmanager_namemanager_department
1Snehdeep KaurORACLE
2Kirti KirtaneFMW
3Abhishek ManishJAVA
4Anupam MishraTESTING
5Akash KadamC#

The following query will display the records of Laptop.

SELECT * FROM Laptop;

The output of the above SELECT query is:

LAPTOPIDNAME                    EMPLOYEEID
101DELLNULL
102HP1002
103LENOVONULL
104HP3003
105DELL4002
106LENOVO4003
107DELL5001
108HPNULL
109DELLNULL
110HPNULL
111LENOVO2002
112LENOVO6003
113HP1003

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
2001PRACHISHARMA55500CHANDIGARHORACLE1
4003RUCHIKAAGARWAL60000DELHIORACLE1
6003NIKITAINGALE65000HYDERABADORACLE1
1003NIKHILVANI50500JAIPURFMW2
2002BHAVESHJAIN65500PUNEFMW2
3002ANUJAWANRE50500JAIPURFMW2
3001PRANOTISHENDE55500PUNEJAVA3
3003DEEPAMJAUHARI58500MUMBAIJAVA3
4002ASHWINIBAGHAT54500NOIDAJAVA3
5002SANKETCHAUHAN70000HYDERABADJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
5001ARCHITSHARMA55500DELHITESTING4
6001RAHULNIKAM54500BANGALORETESTING4
1002VAIBHAVSHARMA60000NOIDAC#5
2003RUCHIKAJAIN50000MUMBAIC#5
5003ROSHANNEHTE48500CHANDIGARHC#5
6002ATISHJADHAV60500BANGALOREC#5
How To Use INNER JOIN In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1002VAIBHAVSHARMA60000NOIDAC#5
2003RUCHIKAJAIN50000MUMBAIC#5
5003ROSHANNEHTE48500CHANDIGARHC#5
6002ATISHJADHAV60500BANGALOREC#5
How To Use INNER JOIN In SQL

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:

EMPLOYEEIDFIRST_NAMESALARYCITYDEPARTMENTLAPTOPIDNAME
1002VAIBHAV60000NOIDAC#102HP
3003DEEPAM58500MUMBAIJAVA104HP
4002ASHWINI54500NOIDAJAVA105DELL
4003RUCHIKA60000DELHIORACLE106LENOVO
5001ARCHIT55500DELHITESTING107DELL
2002BHAVESH65500PUNEFMW111LENOVO
6003NIKITA65000HYDERABADORACLE112LENOVO
1003NIKHIL50500JAIPURFMW113HP
How To Use INNER JOIN In SQL

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:

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERID
1001VAIBHAVIMISHRA65500PUNEORACLE1
2001PRACHISHARMA55500CHANDIGARHORACLE1
4003RUCHIKAAGARWAL60000DELHIORACLE1
6003NIKITAINGALE65000HYDERABADORACLE1
2002BHAVESHJAIN65500PUNEFMW2
3003DEEPAMJAUHARI58500MUMBAIJAVA3
5002SANKETCHAUHAN70000HYDERABADJAVA3
4001RAJESHGOUD60500MUMBAITESTING4
1002VAIBHAVSHARMA60000NOIDAC#5
6002ATISHJADHAV60500BANGALOREC#5
How To Use INNER JOIN In SQL

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.



ADVERTISEMENT
ADVERTISEMENT