SQL INNER JOIN returns the rows from the tables that have matching value in columns.
It eliminates the non matching rows in result set.
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.
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