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 is Join?

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 
  1. CREATE TABLE [dbo].[SubExperts](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Subject] [nvarchar](50) NULL,  
  4. PRIMARY KEY CLUSTERED   
  5. (  
  6.     [Id] ASC  
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  8. ) ON [PRIMARY]  
  9.   
  10. SELECT * FROM [graphdemo].[dbo].[SubExperts]  

Note - Please insert the record as it looks in the below table output. 

Output
 
 
Ex. Ratings 
  1. CREATE TABLE [dbo].[Ratings](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Rating] [int] NULL,  
  4. PRIMARY KEY CLUSTERED   
  5. (  
  6.     [Id] ASC  
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  8. ) ON [PRIMARY]  
  9.   
  10. SELECT * FROM [graphdemo].[dbo].[Ratings]  

 Output

 
Ex. Employees
  1. CREATE TABLE [dbo].[Employees](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Name] [nvarchar](50) NULL,  
  4.     [Age] [int] NULL,  
  5.     [Salary] [money] NULL,  
  6.     [SubExpertId] [int] NULL,  
  7.     [RatingId] [int] NULL,  
  8. PRIMARY KEY CLUSTERED   
  9. (  
  10.     [Id] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13.   
  14. GO  
  15.   
  16. ALTER TABLE [dbo].[Employees]  WITH CHECK ADD FOREIGN KEY([RatingId])  
  17. REFERENCES [dbo].[Ratings] ([Id])  
  18. GO  
  19.   
  20. ALTER TABLE [dbo].[Employees]  WITH CHECK ADD FOREIGN KEY([SubExpertId])  
  21. REFERENCES [dbo].[SubExperts] ([Id])  
  22. GO  
  23.   
  24. SELECT * FROM [graphdemo].[dbo].[Employees]  

 Output

 

We have the following different types of Joins in SQL.

INNER JOIN

An inner join returns only the matching records between two tables and non-matching records are eliminated.

 

Syntax

  1. SELECT ColumnList FROM Table1  
  2. INNER JOIN  
  3. Table2 on Condition  
  4. INNER JOIN  
  5. Table3 on Condition  

 Example

Select those employees with respective subject expertise and rating.

  1. SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject, r.Rating FROM [Employees] e  
  2. INNER JOIN  
  3. [SubExperts]  s on e.SubExpertId = s.Id  
  4. INNER JOIN  
  5. [Ratings] r on e.RatingId = r.Id  

 Output

 
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 employees table, because they have some relationship between SubExperts and Rating table and the rest of the records are eliminated. Obviously, they don’t have a relationship in between.

OUTER JOIN

Outer join can be divided into three categories.

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 keyword. So, the outer keyword is optional.

 

Syntax
  1. SELECT ColumnList FROM Table1  
  2. LEFT JOIN  
  3. Table2 on Condition  
  4. LEFT JOIN  
  5. Table3 on Condition  

 Example

 Select all the employees with respective subject expertise and rating.

  1. SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject, r.Rating FROM [Employees] e  
  2. LEFT JOIN  
  3. [SubExperts]  s on e.SubExpertId = s.Id  
  4. LEFT JOIN  
  5. [Ratings] r on e.RatingId = r.Id  

 Output

 

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 employees table.

b) 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.

 

Syntax
  1. SELECT ColumnList FROM Table1  
  2. RIGHT JOIN  
  3. Table2 on Condition  
  4. RIGHT JOIN  
  5. Table3 on Condition  

 Example

Select all the subjects and the employees who are experts in that.

  1. SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject FROM [Employees] e  
  2. Right JOIN  
  3. [SubExperts]  s on e.SubExpertId = s.Id  

Output 

 

 

After executing the above query, we should get 15 records out of a total 13 records of SubExperts. Additional two records are registered with the same course, ‘LINQ,’ but whoever has a relationship are showing up in  the data, while the rest of the records are showing null value.

FULL JOIN

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.

 

Syntax

  1. SELECT ColumnList FROM Table1  
  2. FULL OUTER JOIN  
  3. Table2 on Condition  

Example

Select all the employees and subject expertise.

  1. SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject FROM [Employees] e  
  2. FULL OUTER JOIN  
  3. [SubExperts]  s  on e.SubExpertId = s.Id  
Output

 

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

Produces the Cartesian product.

Syntax

  1. SELECT ColumnList FROM Table1  
  2. CROSS JOIN  
  3. Table2   

 Example

  1. SELECT e.ID, e.Name, e.Age, e.Salary, s.Subject FROM [Employees] e  
  2. Cross JOIN  
  3. [SubExperts]  s    

Output