Introduction
In this post, we will discuss how to work with joins and will explain the concept with examples in a simple way. I hope this is very useful for beginners and intermediate-level learners so as to help them understand the basic concept.
Prerequisites
Before we start with what a Join is, I assume that we are already aware of SQL table constraints in the database and with that, you can create the basic tables.
What are Joins in SQL?
Basically, SQL Joins are used to retrieve the data from two or more tables based on the common column between them which shows how the tables are related to each other using primary key and foreign key constraints based on the logical relationships.
For example purposes, we have created the following three tables.
Ex. SubExperts
CREATE TABLE [dbo].[SubExperts](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Subject] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SELECT * FROM [graphdemo].[dbo].[SubExperts]
Note. Please insert the record as it looks in the below table output.
Output
![Joins In SQL]()
Ex. Ratings
CREATE TABLE [dbo].[Ratings](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Rating] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SELECT * FROM [graphdemo].[dbo].[Ratings]
Output
![Joins In SQL]()
Ex. Employees
CREATE TABLE [dbo].[Employees](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Age] [int] NULL,
[Salary] [money] NULL,
[SubExpertId] [int] NULL,
[RatingId] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employees] WITH CHECK ADD FOREIGN KEY([RatingId])
REFERENCES [dbo].[Ratings] ([Id])
GO
ALTER TABLE [dbo].[Employees] WITH CHECK ADD FOREIGN KEY([SubExpertId])
REFERENCES [dbo].[SubExperts] ([Id])
GO
SELECT * FROM [graphdemo].[dbo].[Employees]
Output
![Joins In SQL]()
We have the following different types of Joins in SQL.
INNER JOIN in SQL
An inner join returns only the matching records between two tables and non-matching records are eliminated.
![Joins In SQL]()
Syntax
SELECT ColumnList FROM Table1
INNER JOIN
Table2 on Condition
INNER JOIN
Table3 on Condition
Example
Select those employees with respective subject expertise and rating.
SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject, r.Rating FROM [Employees] e
INNER JOIN
[SubExperts] s on e.SubExpertId = s.Id
INNER JOIN
[Ratings] r on e.RatingId = r.Id
Output
![Joins In SQL]()
Whatever columns you want to select between the three above-listed tables, add in the select clause. After executing the above query, we should get six records out of ten records of the employee's table, because they have some relationship between SubExperts and the Rating table, and the rest of the records are eliminated. Obviously, they don’t have a relationship in between.
OUTER JOIN in SQL
Outer join can be divided into three categories.
1. LEFT JOIN or LEFT OUTER JOIN
The Left Join returns all the matching records between two tables and not matching records from the left table. We can use left join or left outer join as keywords. So, the outer keyword is optional.
![Joins In SQL]()
Syntax
SELECT ColumnList FROM Table1
LEFT JOIN
Table2 on Condition
LEFT JOIN
Table3 on Condition
Example
Select all the employees with respective subject expertise and rating.
SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject, r.Rating FROM [Employees] e
LEFT JOIN
[SubExperts] s on e.SubExpertId = s.Id
LEFT JOIN
[Ratings] r on e.RatingId = r.Id
Output
![Joins In SQL]()
Whatever columns you want to select among the three above-listed tables, add in the Select clause. After executing the above query, we should get 10 records out of 10 records of the employee's table.
2. RIGHT JOIN or RIGHT OUTER JOIN
The Right Join returns all the matching records between two tables and non-matching records from the right table.
![Joins In SQL]()
Syntax
SELECT ColumnList FROM Table1
RIGHT JOIN
Table2 on Condition
RIGHT JOIN
Table3 on Condition
Example
Select all the subjects and the employees who are experts in that.
SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject FROM [Employees] e
Right JOIN
[SubExperts] s on e.SubExpertId = s.Id
Output
![Joins In SQL]()
After executing the above query, we should get 15 records out of a total of 13 records of SubExperts. Additional two records are registered with the same course, ‘LINQ,’ but whoever has a relationship is showing up in the data, while the rest of the records are showing null value.
FULL Join in SQL
The full join returns all the matching records between two tables and in addition non-matching records from the left and right tables.
Returns all records when there is a match in either the left or the right table.
![Joins In SQL]()
Syntax
SELECT ColumnList FROM Table1
FULL OUTER JOIN
Table2 on Condition
Example
Select all the employees and subject expertise.
SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject FROM [Employees] e
FULL OUTER JOIN
[SubExperts] s on e.SubExpertId = s.Id
Output
![Joins In SQL]()
We have a total of ten employees and they are good subject experts in some subjects but don't know the rest of the subjects.
Cross Join in SQL
Produces the Cartesian product.
Syntax
SELECT ColumnList FROM Table1
CROSS JOIN
Table2
Example
SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject FROM [Employees] e
Cross JOIN
[SubExperts] s
Output
![Joins In SQL]()
Summary
This article taught us about joins In SQL with different types and code examples.
Reference