What is Sql Joins? With Realtime Examples

Welcome to Quickpickdeal. In this session, we will understand the different types of joins available in SQL Server specifically.

• Cross join
• Inner join
• Outer join with realtime examples.

Joins in SQL Server are used to retrieve data from two or more related tables. Tables are related to each other using foreign key constraints.

Let’s understand this with an example. I have a TblStudent table which contains columns for name, gender, and University_Id. Additionally, there's a TblUniversity table with columns for UniversityName, location, and country_Id. Lastly, there's a TblCountry table with columns for Country_Id and CountryName.

TblStudent and TblUniversity are related using the foreign key University_Id, while TblUniversity and TblCountry are related using the foreign key country_Id. If you're unsure about what a foreign key is, please refer to this post.

For the SQL script to create these tables, you can find it at the end of this post."

So these two are related tables. Now, let’s say I ask you to write a query which will give me output as shown in the below image.
I want to retrieve the name of the student, gender, and the University name. If you look at the required output columns, the first two columns (name and gender) are present in the TblStudent table, while the University name is present in the TblUniversity table. 

So these columns are not coming from a single table; they are spread across two different tables. To retrieve this output, we need to join these two tables in SQL Server.

To join these two tables, we have different types of joins. For example, we have inner join, outer join, and cross join. Additionally, outer joins in SQL Server are divided into three categories: left outer join, right outer join, and full outer join. Alternatively, you can refer to them simply as left join, right join, and full join.

What is inner join?

Now, let's discuss the inner join. Suppose I want the output as shown in the image below. I need the name of the student, their gender, and the University name.

So let’s see how we achieve this using an inner join. In an inner join, only the matching rows between the two tables are retrieved.

For example, if you look at the TblStudent table here, everyone has a University_Id except Jesiah and Rocky.

If you examine the records for Jesiah and Rocky, you'll notice that the University_Id is Null, indicating that these rows do not have a corresponding University_Id in the TblUniversity table.

As a result, when I perform an inner join between these two tables, only the rows that have matching University_Id values are retrieved.

SELECT StudentName, Gender, UniversityName
FROM TblStudent
Inner JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
or

SELECT StudentName, Gender, UniversityName
FROM TblStudent
JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id

And as you can see in the output, Jesiah and Rocky's records are not included. Why? Because their University_Id values do not match with any records in the University_Id column of the TblUniversity table.

So inner join will only give you matching rows between both the tables involved in a join.

Query Explanation:

Now, let's write the inner join query. We have two tables: TblStudent with 8 rows and TblUniversity with 7 rows. We want to retrieve the name, gender, and University name columns from the TblStudent table. While the first two columns are present in TblStudent, the University name column is in TblUniversity. Therefore, we need to join these two tables.

To join them, we use the JOIN keyword, specifying TblStudent as the left table and TblUniversity as the right table. Then, we define the join condition using the ON clause.

The common column for joining is the University_Id column.

We have the University_Id column, so use that to look up the TblUniversity table and the University_Id column within that table.so it’s pretty simple.

SQL Inner join with more than two tables

Now we want the country name also in the result, then we also need to join the TblCountry.

SELECT StudentName, Gender, UniversityName,Location,CountryName FROM TblStudent 
Inner JOIN TblUniversity 
ON TblStudent.University_Id = TblUniversity.University_Id
INNER JOIN TblCountry
      ON TblUniversity.Country_Id=TblCountry.Country_Id

What is left join?

Now, let's consider a scenario where I not only want the matching rows but also the non-matching rows from the TblStudent table. In other words, I want all the students, regardless of whether they are assigned to a University or not.

To achieve this, we use a left join. A left join retains all the matching rows from both tables, as well as the non-matching rows from the left table.

The syntax remains the same as for the inner join, except that we replace 'INNER JOIN' with 'LEFT JOIN'.

SELECT StudentName, Gender, UniversityName
FROM TblStudent
Left Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
or

SELECT StudentName, Gender, UniversityName
FROM TblStudent
Left OUTER Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id

Now execute the above query, and observe the number of records retrieved. If you look at the output now, we should have obtained records for both Jesiah and Rocky as well.

However, if you examine the UniversityName column, it is NULL. This makes sense because they do not have a University_Id; they are not assigned to a University yet.

So the University name will be NULL. Left join basically returns all the matching rows between both tables, plus non-matching rows from the left table.

The picture depicts exactly that scenario. When you use a left join, you can use just 'left join' or 'left outer join'; the query results will not be affected. The 'outer' keyword is optional.

SQL Left join with more than two tables

SELECT StudentName, Gender, UniversityName,Location,CountryName FROM TblStudent 
Left JOIN TblUniversity 
ON TblStudent.University_Id = TblUniversity.University_Id
Left JOIN TblCountry
      ON TblUniversity.Country_Id=TblCountry.Country_Id

What is Right join?

If you look at the two tables in the image below, there is a UniversityName called Massachusetts Institute of Technology (MIT) with University_Id = 1. As you can see, no student in the TblStudent table belongs to this university.

If there is a requirement where someone asks for all the matching records between both tables, plus non-matching records from the right table, then from the TblUniversity table, they would want the Massachusetts Institute of Technology (MIT) record as well, irrespective of whether there is a student assigned to that university or not. In other words, they want all the rows from the right table.

Let’s see what happens if we convert this left outer join to a right outer join.

SELECT StudentName, Gender, UniversityName
FROM TblStudent
Right OUTER Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id

or

SELECT StudentName, Gender, UniversityName
FROM TblStudent
Right Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id

When we execute this query, we should get around 7 records. Looking at the output, we can see all the matching rows, as well as the row for Massachusetts Institute of Technology (MIT).

However, if you examine the 'name' and 'gender' columns, you'll notice they are NULL. This is because no students are assigned to this university, so naturally, there are no names or genders associated with it.

Therefore, whenever the requirement is to return all the matching rows between both tables involved in a join, plus non-matching rows from the right table, that's when we use a right outer join.

SQL Right join with more than two tables

SELECT StudentName, Gender, UniversityName,Location,CountryName FROM TblStudent 
Right JOIN TblUniversity 
ON TblStudent.University_Id = TblUniversity.University_Id
Right JOIN TblCountry
      ON TblUniversity.Country_Id=TblCountry.Country_Id

What is Full JOIN?

So you might have guessed by now, if I want all the matching rows between both tables, plus non-matching rows from the left table and non-matching from the right table, then what type of join do I use? A full outer join gives me exactly that result.

A full outer join will give you all the records from both the left and right tables, including the non-matching rows.

How do we write a full outer join query? Just specify full outer join.

SELECT StudentName, Gender, UniversityName
FROM TblStudent
Full Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id

When we execute this query, we should get around 9 records: 6 matching records, two non-matching from the left, and one non-matching from the right table.

So far, we have seen inner join, left join, right join, outer join, and full outer join. Apart from all these types of joins, we also have another join called Cross Join.

SQL FULL join with more than two tables

SELECT StudentName, Gender, UniversityName,Location,CountryName FROM TblStudent 
Full JOIN TblUniversity 
ON TblStudent.University_Id = TblUniversity.University_Id
Full JOIN TblCountry
      ON TblUniversity.Country_Id=TblCountry.Country_Id

What is Cross Join?

So what does a cross join do and how do I write that query? First, let’s write the query, then we'll examine the output, and finally, we’ll discuss what a cross join is going to do.

If it’s a cross join, instead of saying full outer join, all I need to do is use the keyword `CROSS JOIN`. Now, remember, a cross join will not have an `ON` clause. I’ll explain in a bit why a cross join shouldn’t have an `ON` clause.

SELECT StudentName, Gender, UniversityName
FROM TblStudent
Cross Join TblUniversity

When I execute this query, notice that we get 56 rows in the output.And if you recall, how many rows are there in these tables? There are 8 rows in the TblStudent table, and 7 rows in the TblUniversity table.

When we execute the cross join query, we are getting 56 rows. So basically what's happening is that the number of rows in the TblStudent table is being multiplied by the number of rows in the TblUniversity table. Therefore, a cross join will give us the Cartesian product of the tables that are involved in the join.

In summary, a cross join returns the Cartesian product of the tables involved in the join, whereas an inner join returns only the matching rows between the tables.

Refer to the picture below, which will make understanding of joins much easier. If you look at the inner join, it returns only matching rows, while left join returns matching rows plus non-matching rows from the left table.

Right join matching rows, plus non-matching from the right table, but as full join matching rows between both the tables and non-match matching from the left and non-matching from the right.

Table Script

GO
/****** Object:  Table [dbo].[TblCountry]    Script Date: 09/11/2020 1:40:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblCountry](
    [Country_Id] [int] NOT NULL,
    [CountryName] [nvarchar](max) NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
    [Country_Id] ASC
)
)
GO
/****** Object:  Table [dbo].[TblStudent]    Script Date: 09/11/2020 1:40:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblStudent](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [StudentName] [nvarchar](max) NOT NULL,
    [Gender] [nvarchar](50) NULL,
    [University_Id] [int] NULL,
 CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
)
GO
/****** Object:  Table [dbo].[TblUniversity]    Script Date: 09/11/2020 1:40:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUniversity](
    [University_Id] [int] IDENTITY(1,1) NOT NULL,
    [UniversityName] [nvarchar](max) NULL,
    [Location] [nvarchar](max) NULL,
    [Country_Id] [int] NULL,
 CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED 
(
    [University_Id] ASC
)
) 
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (1, 'USA')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (2, 'United Kingdom')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (3, 'China')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (4, 'India')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (5, 'Australia')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (6, 'Japan')
GO
SET IDENTITY_INSERT [dbo].[TblStudent] ON 
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (1, 'Jesiah', 'Male', NULL)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (2, 'Evelyn', 'Female', 2)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (3, 'Advika', 'Female', 4)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (4, 'Bowie', 'Male', 3)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (5, 'Adweta', 'Female', 6)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (6, 'Alaric', 'Male', 5)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (7, 'Aarna', 'Female', 7)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (8, 'Rocky', 'Male', NULL)
GO
SET IDENTITY_INSERT [dbo].[TblStudent] OFF
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] ON 
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (1, 'Massachusetts Institute of Technology (MIT)', '77 Massachusetts Ave, Cambridge, MA 02139', 1)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (2, 'Stanford University', ' Stanford University Stanford, CA', 1)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (3, 'University of Delhi', 'Benito Juarez Marg, South Campus, South Moti Bagh, New Delhi,', 4)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (4, 'The Australian National University', 'Canberra ACT 0200, Australia', 5)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (5, 'Tsinghua University', '30 Shuangqing Rd, Haidian District, Beijing', 3)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (6, 'University of Tokyo', '7 Chome-3-1 Hongo, Bunkyo City, Tokyo 113-8654, Japan', 6)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (7, 'University of Oxford', ' Oxford OX1 2JD, United Kingdom', 2)
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] OFF
GO
ALTER TABLE [dbo].[TblStudent]  WITH CHECK ADD  CONSTRAINT [FK_TblStudent_University] FOREIGN KEY([University_Id])
REFERENCES [dbo].[TblUniversity] ([University_Id])
GO
ALTER TABLE [dbo].[TblStudent] CHECK CONSTRAINT [FK_TblStudent_University]
GO
ALTER TABLE [dbo].[TblUniversity]  WITH CHECK ADD  CONSTRAINT [FK_TblUniversity_Country] FOREIGN KEY([Country_Id])
REFERENCES [dbo].[TblCountry] ([Country_Id])
GO
ALTER TABLE [dbo].[TblUniversity] CHECK CONSTRAINT [FK_TblUniversity_Country]
GO

That’s it for today. Thank you for listening. Have a great day.