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?

The Inner Join is used to returns only the matching records from both the tables by removing the non-matching records. That means Inner join results only the matching rows from both the tables involved in the join. The following diagram shows the 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 Inner Join or JOIN keyword to perform Inner Join. If you use only the JOIN keyword, then it is also going to perform Inner JOIN 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 some data. Here the CompanyId column from 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

Write a query, to retrieve CandidateId, FullName, CompanyId, and CompanyName from Company and Candidate tables. The output of the query should be as shown below.

Following is the Query which is an example of Inner Join that is joining the Company and Candidate tables and will give the results 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. 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 test 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 get 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. 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 ) 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.

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 with Examples. I hope you enjoy this article.