Inner Join in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Inner Join in SQL Server with a real-time example

In this article, I am going to discuss the Inner Join in SQL Server with a real-time example. Please read our previous article where we discussed the SQL Server Cross Join with an example. 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?

What is Inner Join?

The Inner Join in SQL Server 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?

Let us understand how to implement Inner Join with an example. We are going to use the following Company and Candidate tables in this demo to understand Inner Join.

Please use the below SQL Script to create Company and Candidate tables and populate these tables with the required test data. Here the CompanyId column from the Candidate Table is a foreign key which 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

If you want to select all the rows from the Left table (In our example it is the Candidate Table) that have a non-null foreign key value then you need to use the 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.

Let us have a look at the below Query which is an example of Inner Join joining the Company and Candidate tables.

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

When you execute the above Inner Join Query, then it will produce the following result set.

If you look at the above output, we got only 5 rows. We did not get the 2 rows which have the NULL value in the CompanyId column.  Instead of using INNER JOIN keyword we can also use JOIN keyword as shown below. 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.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 in are simply eliminated.

In the next article, I will 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. I hope you enjoy this article.