What is INNER JOIN in SQL Server ?

In relational database we store data in multiple table with some relationship between them.
To retrieve the common (union) data from multiple table we use INNER JOIN in SQL Server. We can use only JOIN instead of INNER JOIN in SQL Server because JOIN default indicate INNER JOIN.

Below image will give you brief idea regarding inner join. We have to tables and it is showing only common data from both tables.

inner join in sql server

Syntax of JOIN / INNER JOIN

SELECT < list of columns >
FROM table1 AS t1
INNER JOIN
table2 AS t2 ON t1.column1Value = t2.Column1Value AND t1.column2Value = t2.Column2Value
WHERE < condition if any >

Note : We use the INNER JOIN keyword to join two or more table and ON keyword is use to add condition. We can add multiple condition using AND keyword.


Consider below two result set of tblStudentDetails table and tblStudentMarks table, having student and student marks details.
Select the record from table Student Id, Student Name, Subject and Marks student who having marks.

inner join exmple on two table in sql server

Example

SELECT sd.StudentId, sd.StudentName, sm.Subject, sm.Marks
FROM
tblStudentDetails AS sd
INNER JOIN
tblStudentMarks AS sm ON sd.StudentId = sm.StudentId

Output

inner join exmple output


Note : If you observe above JOIN statement then you can see JOIN result set not show the data of Jyo and Sudarshan because both student not having marks data.
It mean INNER JOIN it return the common data from both table.


Jon on more than two table in SQL server.

Consider below three result set of tblStudentDetails table, tblStudentMarks table and tblStudentContact table.
Select the matching record from table Student Id, Student Name, Subject and Marks student who having marks and also Student Contact.

inner join on more than two tables in sql server

Example

SELECT sd.StudentId, sd.StudentName, sm.Subject, sm.Marks, sc.StudentContact
FROM
tblStudentDetails AS sd
INNER JOIN
tblStudentMarks AS sm ON sd.StudentId = sm.StudentId
INNER JOIN
tblStudentContact AS sc ON sd.StudentId = sc.StudentId

Output

The INSERT statement conflicted with the CHECK constraint


Note : In above example only one student Yogesh having record in all three table, so only one record show in output of JOIN.


INNER JOIN with where condition.

Consider the below result set and select student whose marks greater than 50 and subject in Math and Science .

inner join with condition in sql server

Example

SELECT sd.StudentId, sd.StudentName, sm.Subject, sm.Marks
FROM
tblStudentDetails AS sd
INNER JOIN
tblStudentMarks AS sm ON sd.StudentId = sm.StudentId
WHERE sm.Marks > 50 AND sm.Subject IN ('Math','Science')

Output

inner join with condition in sql server output