Working with Joins in SQL

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

12 Comments

Join the conversation! Your thoughts help the community grow.

Kalai Yarasi8y ago
Very good explanation
Shrimant Telgave8y ago
Good start and explained very well......keep it up.
Khaja Moizuddin8y ago
Good start Jitendra....
- Jitendra Waghale8y ago
  Thank You!!
Sumit Rajguru8y ago
Very good article. Thanx for sharing.
- Jitendra Waghale8y ago
  Thank you!!
Suraj Kumar8y ago
Good article thanks for sharing
- Jitendra Waghale8y ago
  Thank you!!
Prasad Krishnarao8y ago
Nice Article
- Jitendra Waghale8y ago
  Thanks you
Jignesh Kumar8y ago
Well explained. pretty clear
- Jitendra Waghale8y ago
  Thank you very much!!