SQL INNER JOIN

SQL INNER JOIN returns the rows from the tables that have matching value in columns. 

It eliminates the non matching rows in result set.

Syntax

 SELECT COLUMN(S),...
 FROM TABLE T1 INNER JOIN TABLE T2 
 ON T1.COLUMN = T2. COLUMN 
 WHERE CONDITIONS..

 

Inner Join gives us only matching rows in both the tables, as shown in below diagram.

Lets look at an example of Inner Join in SQL Server.

Here we have two tables Department and Emoloyee_Info.

CREATE TABLE dbo.DEPARTMENT
(DEPT_ID INT PRIMARY KEY,
DEPT_NAME VARCHAR(50),
DEPT_CODE VARCHAR(15))

INSERT INTO dbo.DEPARTMENT(DEPT_ID, DEPT_NAME, DEPT_CODE)
VALUES(501, 'IT', '001'),
(502, 'FINANCE', '002'),
(503, 'HR', '003'),
(504, 'MIS', '004')

CREATE TABLE dbo.EMPLOYEE_INFO (
EMP_ID INT PRIMARY KEY, 
EMP_NAME VARCHAR(50), 
EMP_CODE VARCHAR(15),
DEPT_ID INT REFERENCES dbo.DEPARTMENT(DEPT_ID)
)  

INSERT INTO dbo.EMPLOYEE_INFO (EMP_ID, EMP_NAME, EMP_CODE, DEPT_ID )
VALUES (1, 'MANISH SINGH', 'EN-0034', 501),
(2, 'MANOJ RAWAT', 'EN-0035', 501),
(3, 'RAJESH KUMAWAT', 'EN-0036', 501),
(4, 'SURAJ K', 'EN-0037', 502),
(5, 'RUPESH SINGH', 'EN-0038', 502),
(6, 'ARJUN PAL', 'EN-0039', 503),
(7, 'GURPREET KAUR', 'EN-0040', 504),
(8, 'TRILOK KUMAR', 'EN-0041', 504),
(9, 'RANUK KHAN', 'EN-0041', null),
(10, 'MANDEEP GREWAL', 'EN-0041', null)

Suppose you want to see the employee details Lets say, EMP_ID, EMP_NAME, EMP_CODE, and DEPT_NAME in result set, and employee must  have any department name that should be present in DEPARTMENT table.

If you query EMP_INFO table then you will get EMP_ID, EMP_NAME, EMP_CODE and DEPT_ID but Department name is not present in this table , so to look up for Department name you need to query DEPARTMENT table.

So, To get DEPT_NAME you need to Join both table together based on a column that have matching rows in both table.

As you can see, DEPT_ID (Foreign key) column of EMPLOYEE_INFO table having matching rows with the DEPT_ID(Primary key) column in DEPARTMENT table.

Following statement use INNER JOIN clause to join both tables and returns employee details, also eliminates those employee whose DEPT_ID does not exist in DEPARTMENT table.

SELECT EMP.EMP_ID, EMP.EMP_NAME, EMP.EMP_CODE, DEPT.DEPT_NAME 
FROM dbo.EMPLOYEE_INFO EMP INNER JOIN dbo.DEPARTMENT DEPT
ON EMP.DEPT_ID = DEPT.DEPT_ID

 

As you can see, It returns eight records in the results set for matching DEPT_ID in both tables, and eliminates two records that is ‘RANUK KHAN’ and ‘MANDEEP GREWAL’ as their DEPT_ID do not match with DEPT_ID in DEPARTMENT table. 

 

INNER JOIN WITH WHERE CLAUSE

To get employee details who belongs to IT department.

SELECT EMP.EMP_ID, EMP.EMP_NAME, EMP.EMP_CODE, DEPT.DEPT_NAME 
FROM dbo.EMPLOYEE_INFO EMP INNER JOIN dbo.DEPARTMENT DEPT 
ON EMP.DEPT_ID = DEPT.DEPT_ID
WHERE DEPT.DEPT_ID =501

As you can see, It returns the details of those employee who belongs to IT department.

Recommended for you

JOINS OVERVIEW

FULL JOIN

LEFT JOIN

RIGHT JOIN

 

 1,380 total views,  2 views today