Joins in SQL Server and Different Types of Joins in SQL Server

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, every developer must know about SQL Joins and how we can use different joins to fetch data from two or more tables, based on logical relationships between the tables.

In this article, I am going to explain about the most common SQL Joins and how you can use joins in a query.

Types of SQL Joins

Here I have listed four different types of commonly used SQL Joins.

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 2 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.

Ref: 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

employee master

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

list of 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

inner join

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

Also Read: 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

left outer join

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

right outer join

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

full outer join

Conclusion

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.

That is it. Thanks for reading.

Previous - SQL SERVER - Database file size and Log file sizeNext - SQL Server – Saving changes is not permitted



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner

Related Posts: