This tutorial will explain how to use INNER JOIN clause in a SQL query and how to use them with the examples.
The INNER JOIN statement(s) is combining rows from two or more tables based on the given column value matches.
Lets create a sample table and add some rows
CREATE TABLE Users (
UserID int NOT NULL,
Name nvarchar(50),
PRIMARY KEY (UserID)
);
CREATE TABLE Offices (
OfficeID int NOT NULL,
Name nvarchar(50),
PRIMARY KEY (OfficeID)
);
Lets now create table with foreign keys to use UserID and LocationID column as reference value
CREATE TABLE Tasks (
TaskID int NOT NULL PRIMARY KEY,
Title nvarchar(50),
UserID int,
OfficeID int NULL,
CONSTRAINT FK_Tasks_Users FOREIGN KEY(UserID) REFERENCES Users(UserID),
CONSTRAINT FK_Tasks_Offices FOREIGN KEY(OfficeID) REFERENCES Offices(OfficeID)
);
INSERT INTO Users
(UserID, Name)
VALUES
(1, 'User1'),
(2, 'User2'),
(3, 'User3'),
(4, 'User4'),
(5, 'User5'),
(6, 'User6')
INSERT INTO Offices
(OfficeID, Name)
VALUES
(1, 'Office1'),
(2, 'Office2'),
(3, 'Office3')
INSERT INTO Tasks
(TaskID, Title, UserID, OfficeID)
VALUES
(1, 'Task1', 1, 3),
(2, 'Task2', 1, 2),
(3, 'Task3', 2, 3),
(4, 'Task4', 4, 1),
(5, 'Task5', 5, NULL),
(6, 'Task6', 6, 2)
This is the general statement to use INNER JOIN with tables
SELECT *
FROM table1 INNER JOIN table2
ON table1.column_name = table2.column_name;
The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.
In the following example you can see rows have been combined with UserID columns on both tables
SELECT *
FROM Tasks t INNER JOIN Users u
ON t.UserID = u.UserID
| TaskID | Title | UserID | UserID | Name |
-------------------------------------------
1 Task1 1 1 User1
2 Task2 1 1 User1
3 Task3 2 2 User2
4 Task4 4 4 User4
5 Task5 5 5 User5
6 Task6 6 6 User6
As you can it is listing all columns from both tables as '*' charachter used for return columns. We can return the columns we would like to return as following
SELECT t.Title TaskTitle, u.Name UserName
FROM Tasks t INNER JOIN Users u
ON t.UserID = u.UserID
| TaskTitle | UserName |
------------------------
Task1 User1
Task2 User1
Task3 User2
Task4 User4
Task5 User5
Task6 User6
Lets use INNER JOIN on multiple tables now. Below statement it retrieve all tasks than combine them with Users on UserID and Offices on OfficeID column.
SELECT
t.Title TaskTitle, u.Name UserName, o.Name OfficeName
FROM Tasks t
INNER JOIN Users u ON t.UserID = u.UserID
INNER JOIN Offices o ON t.OfficeID = o.OfficeID
| TaskTitle | UserName | OfficeName |
-------------------------------------
Task1 User1 Office3
Task2 User1 Office2
Task3 User2 Office3
Task4 User4 Office1
Task6 User6 Office2
Task5 has not been listed as there is no equavilient record on Offices tables. To display all records from previous statements we can use LEFT JOIN
SELECT
t.Title TaskTitle, u.Name UserName, o.Name OfficeName
FROM Tasks t
INNER JOIN Users u ON t.UserID = u.UserID
LEFT JOIN Offices o ON t.OfficeID = o.OfficeID
| TaskTitle | UserName | OfficeName |
-------------------------------------
Task1 User1 Office3
Task2 User1 Office2
Task3 User2 Office3
Task4 User4 Office1
Task5 User5 NULL
Task6 User6 Office2