Back to: SQL Server Tutorials

SQL Server Inner Join with real-time examples

In this article, I am going to discuss the SQL Server Inner Join with a real-time example. Please read our previous article where we discussed the SQL Server Cross Join with an example. The SQL Server Inner join is used to returns only the matching rows from both the tables while the Non-matching rows are eliminated. That means SQL Server Inner join results only the matching rows from both the tables involved in the join.

Let us understand SQL Server Inner join with an example.

We are going to use the following Company and Candidate tables in this demo.

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 SQL Server Inner Join. To simplify this we can say that if we want to retrieve all the matching rows from both the tables then we need to use the Inner Join in SQL Server.

Let us have a look at the below Query which is an example of SQL Server 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 SQL Server 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 SQL Server Inner Join only returns the matching records from both the tables involved in the join. The Non-matching rows in SQL Server Inner Join are simply eliminated. In the next article, I will discuss Left Outer Join in SQL Server with one real-time example.

SUMMARY

In this article, I try to explain the Inner Join in SQL Server. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.