Inner Join in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Inner Join in SQL Server with Examples

In this article, I am going to discuss the Inner Join in SQL Server with Examples. Please read our previous article where we discussed the Basics of SQL Server Join. As part of this article, we are going to discuss the following pointers in detail.

1. What is Inner Join?
2. How to implement Inner Join in SQL Server?
3. When do we need to use Inner JOIN?
4. Example to Understand Inner JOIN
5. How to Join Three Tables in SQL Server.

What is Inner Join in SQL Server?

The Inner Join in SQL Server is used to returns only the matching rows from both the tables involved in the join by removing the non-matching rows. That means Inner join results only the matching rows from both the tables involved in the join. The following diagram shows the pictorial representation of Inner Join.

How to implement Inner Join in SQL Server?

Please have a look at the following image which shows the syntax of SQL Server Inner Join. Here, you can use either the INNER JOIN or JOIN keyword to perform Inner Join. If you use only the JOIN keyword, then it is also going to perform the Inner JOIN operation in SQL Server.

When do we need to use Inner JOIN in SQL Server?

If you want to select all the rows from the Left table that have a non-null foreign key value then you need to use SQL Server Inner Join. To simplify this we can say that if you want to retrieve all the matching rows from both the tables then you need to use the Inner Join. 

Example to Understand SQL Server Inner JOIN:

Let us understand how to implement Inner Join in SQL Server with an example. To understand Inner Join in SQL Server, we are going to use the following Company and Candidate tables.

Please use the below SQL Script to create Company and Candidate tables and populate these tables with the required sample data. Here the CompanyId column of the Candidate Table is a foreign key that is referencing the CompanyId column of the Company Table.

CREATE TABLE Company
(
    CompanyId TinyInt Identity Primary Key,
    CompanyName Nvarchar(50) NULL
)
GO
INSERT Company VALUES('DELL')
INSERT Company VALUES('HP')
INSERT Company VALUES('IBM')
INSERT Company VALUES('Microsoft')
GO

CREATE TABLE Candidate
(
    CandidateId tinyint identity primary key,
    FullName nvarchar(50) NULL,
    CompanyId tinyint REFERENCES Company(CompanyId)
)
GO
INSERT Candidate VALUES('Ron',1)
INSERT Candidate VALUES('Pete',2)
INSERT Candidate VALUES('Steve',3)
INSERT Candidate VALUES('Steve',NULL)
INSERT Candidate VALUES('Ravi',1)
INSERT Candidate VALUES('Raj',3)
INSERT Candidate VALUES('Kiran',NULL)
GO

Now, our requirement is to write a query, to retrieve the CandidateId, FullName, CompanyId, and CompanyName from Company and Candidate tables. The output of the query should be as shown below. That means we want only the matching records from both tables.

Following is the Query which is an example of Inner Join that is joining the Company and Candidate tables and will give the result as shown in the above image.

SELECT  Cand.CandidateId, 
  Cand.FullName, 
  Cand.CompanyId, 
  Comp.CompanyName
FROM	Candidate Cand
INNER JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId;

If you look at the above output, we got only 5 rows. We did not get the 2 rows that have the NULL value in the CompanyId column. Instead of using the INNER JOIN keyword, we can also use the JOIN keyword as shown in the below SQL Query. JOIN or INNER JOIN means the same. But it’s always better to use INNER JOIN, as this explicitly specifies your intention.

SELECT Cand.CandidateId, 
       Cand.FullName, 
       Cand.CompanyId, 
       Comp.CompanyName 
FROM Candidate Cand 
JOIN Company Comp 
ON Cand.CompanyId = Comp.CompanyId

So, in short, we can say that the Inner Join is used to returns only the matching records from both the tables involved in the join. The non-matching rows are simply eliminated.

Joining three Tables in SQL Server:

Now we will see how to JOIN three tables in SQL Server. Already we have Company and Candidate tables. Let us introduce the third table i.e. the Address table. So, we are going to use the following Address table along with the Company and Candidate tables.

Please use the below SQL Script to create and populate the Address table with the required sample data.

CREATE TABLE Address
(
    AddressId INT IDENTITY PRIMARY KEY,
    CandidateId tinyint REFERENCES Company(CompanyId),
    Country VARCHAR(50),
    State VARCHAR(50),
    City VARCHAR(50),
)
GO

INSERT INTO Address Values (1, 'India', 'Odisha', 'BBSR');
INSERT INTO Address Values (2, 'India', 'Maharashtra', 'Mumbai');
INSERT INTO Address Values (3, 'India', 'Maharashtra', 'Pune');
INSERT INTO Address Values (4, 'India', 'Odisha', 'Cuttack');
GO

The following is the syntax to join three tables in SQL Server.

Example: Inner Joining Candidate, Company, and Address tables

The following Inner Join SQL Query Joining Candidate, Company, and Address tables.

SELECT          Cand.CandidateId, 
  Cand.FullName, 
  Cand.CompanyId, 
  Comp.CompanyName,
  Addr.Country,
  Addr.State,
  Addr.City
FROM	Candidate Cand
INNER JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId
INNER JOIN Address Addr
ON Addr.CandidateId = Cand.CandidateId;

When you execute the above query, you will get the following output. Now you can see, we got only three records, this is because the following three records exist in all the three tables.

It is also possible to use Different types of Joins while joining three tables in SQL Server. For example, in the below SQL Query, we perform the first join (between Candidate and Company) using Inner JOIN and the second join (between Candidate and Address) operation using LEFT JOIN.

SELECT  Cand.CandidateId, 
  Cand.FullName, 
  Cand.CompanyId, 
  Comp.CompanyName,
  Addr.Country,
  Addr.State,
  Addr.City
FROM	Candidate Cand
INNER JOIN Company Comp
ON Cand.CompanyId = Comp.CompanyId
LEFT JOIN Address Addr
ON Addr.CandidateId = Cand.CandidateId;

When you execute the above SQL Query, you will get the following output. In this case, for the left join between Candidate and Campany, it will take null values for Country, State, and City column which does not have any reference.

Note: You need to use Inner JOIN when you want to retrieve only the matching records from both the tables involved in the JOIN by eliminating the non-matching records from both the tables. In most real-time applications, we need to use this JOIN.

In the next article, I am going to discuss Left Outer Join in SQL Server with one real-time example. Here, in this article, I try to explain the Inner Join in SQL Server with Examples. I hope you enjoy this article.