What are Joins in SQL Server and how many types of Joins?

Last edited: 23rd Jan 2023

Whether you are a frontend developer or a dedicated database developer, we all instinctively try to have access to database objects, particularly the tables, and fetch data from it. However, sometimes we are required to fetch data from multiple tables. Therefore, you must know about SQL Joins and how we can use it.

Types of SQL Joins

There are four different types of JOINS in SQL Server.

01) INNER JOIN
02) LEFT OUTER JOIN
03) RIGHT OUTER JOIN
04) FULL OUTER JOIN

Before we start with our examples, we need to create two tables. As I have said in the beginning of this article, Joins are based on logical relationships between two or more tables. You can create more tables if you wish.

Reference: First create the Employee table in your database. I have already created the table before, as I use it repeatedly in my examples.

SELECT *FROM dbo.Employee

The second table is a list of Books.

CREATE TABLE dbo.Books
(
    BookID INT PRIMARY KEY,
    BookName VARCHAR(50) NULL,
    Category VARCHAR(50) NULL,
    EmpID INT NULL,
    DateIssued DATETIME NULL
)

Now, add few rows to the table.

INSERT INTO Books (BookID, BookName, Category, EmpID, DateIssued)
	SELECT 1, 'The DNA of Unix Programming', 'IT',4, '2013-01-08 15:06:20.547'
INSERT INTO Books (BookID, BookName, Category, EmpID, DateIssued)
	SELECT 2, 'The Hacker Crackdown', 'IT',4, '2013-01-09 14:28:22.480'	
INSERT INTO Books (BookID, BookName, Category, EmpID, DateIssued)
	SELECT 3, 'History of Economic Meltdown', 'ACCOUNTS',2, '2013-01-09 08:41:09.373'
INSERT INTO Books (BookID, BookName, Category)
	SELECT 4, 'Business of Basic Economics', 'ACCOUNTS'
INSERT INTO Books (BookID, BookName, Category, EmpID, DateIssued)
	SELECT 5, 'Hospitality Accounts', 'ACCOUNTS',6, '2012-12-31 14:57:10.610'
INSERT INTO Books (BookID, BookName, Category)
	SELECT 6, 'The Alejandra Variations', 'SCIENCE'
INSERT INTO Books (BookID, BookName, Category, EmpID, DateIssued)
	SELECT 7, 'Ancient Echoes', 'ACCOUNTS',6, '2012-12-31 14:57:10.610'

Output

SELECT *FROM dbo.Books

Now, let’s work with various SQL joins that I have defined above.

INNER JOIN

INNER JOIN will return rows which have a matching ID from both the tables. Rest all the rows will be ignored. This is the default join from the list of all the available joins.

SELECT Emp.EmpID, Emp.EmpName, Emp.Department, UPPER(Bk.BookName) [Book Issued], Bk.DateIssued 
FROM dbo.Employee Emp
INNER JOIN dbo.Books Bk ON Emp.EmpID = Bk.EmpID ORDER BY Emp.EmpID

The above query has fetched Employees who have been issued some books. Since the Employee ids are common in both the tables.

Similar example: Show Date and Time as Blank instead of 1900-01-01 in SQL Server

LEFT OUTER JOIN

The LEFT OUTER JOIN will return all the rows from table Employee irrespective of any matches from the table Books. The example below shows the list of all the “Employees” even if they have not been issued any books.

SELECT Emp.EmpID, Emp.EmpName, Emp.Department, UPPER(Bk.BookName) [Book Issued], Bk.DateIssued 
FROM dbo.Employee Emp
LEFT OUTER JOIN dbo.Books Bk ON Emp.EmpID = Bk.EmpID

RIGHT OUTER JOIN

A RIGHT OUTER JOIN is the reverse of a LEFT OUTER JOIN. It will show the list of all the available “Books” irrespective of a book being issued to any employee.

SELECT Emp.EmpID, Emp.EmpName, Emp.Department, UPPER(Bk.BookName) [Book Issued], Bk.DateIssued 
FROM dbo.Employee Emp
RIGHT OUTER JOIN dbo.Books Bk ON Emp.EmpID = Bk.EmpID

FULL OUTER JOIN

FULL OUTER JOIN is a combination of both LEFT OUTER JOIN and RIGHT OUTER JOIN. That means it will return rows from both Employee and Books table. The output of the below query, as you see, shows the list of all employees and cache of books the company has. You need to very carefull when running this query on tables which have many rows.

SELECT Emp.EmpID, Emp.EmpName, Emp.Department, UPPER(Bk.BookName) [Book Issued], Bk.DateIssued 
FROM dbo.Employee Emp
FULL OUTER JOIN dbo.Books Bk ON Emp.EmpID = Bk.EmpID

Joins can be very expensive in terms of performance. Badly written Joins on tables with many rows, can put immense pressure on the server and can consume lots of disc space.

← PreviousNext →