What is Sql Joins? With Realtime Examples

Welcome to Appsloveworld, In this session we will understand the different types of joins that are available and SQL server specifically

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

Joins in the SQL server are used to retrieve data from two or more related tables. As we know that tables are related to each other using foreign key constraints.

Let’s understand with an example,I have TblStudent table which has got name, gender, and University_Id columns. and then I have the TblUniversity table which has got the UniversityName, location  and country_Id. And the last table is the TblCountry table which has got Country_Id and CountryName columns

TblStudent and TblUniversity  are related using foreign key University_Id and TblUniversity and TblCountry are related using foreign key country_Id. Now, if you’re not sure what foreign key is, please read this post.

If you want SQL script to create these tables, you can find the script at end of the 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 the name of the student, gender, and the University name. And if you look at the output columns that I require, the first 2 columns name, gender, they are present in the TblStudent, whereas the University name is present in the TblUniversity table.

So all these columns are not coming from a single table. They are spread across two different tables. So obviously, if I have to retrieve output, then I will have to join these two tables.

To join these two tables in SQL server, we have got different types of joins.

For example, we have inner join, outer join and Cross joins and again the outer joins in the SQL server are divided into three different categories i.e Left outer join, right outer join, full outer join or you can just say left, join, right join, full join.

What is inner join?

Now let’s talk about inner join. Now let’s say, I want output like below image, I want the name of the student, the gender, and the University name.

So let’s see how we do this using an inner join. If you have two tables, in the inner join the matching rows between the two tables are retrieved.

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

If you look at Jesiah and Rocky’s record, the University_Id is Null, which means these rows don’t have a matching University_Id from the TblUniversity table.

So when I join these two tables using inner join, only the matching rows between these two tables 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 if you look at the output, I don’t get Jesiah and Rocky’s records.Why? Because there University_Id does not match with the University_Id column in 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 see how to write the inner join query itself. So if you look at the two tables that we have,

In the TblStudent table, we have got 8 rows and in the TblUniversity table, we have got 7 rows. And what do we want? We want the name, gender and University name columns in the output From team TblStudent.

The first 2 columns are present in the TblStudent table. But the University name column is present in the TblUniversity table. So I will have to join with that table and to join the table. You use the join keyword.

So we are saying I want to join the TblStudent table with the TblUniversity table.

Now if you want to join these two tables, how do you want to join these two tables? What’s the common column between them? The common column is the University_Id column and to specify the join condition you use the on clause.

 

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 say my requirement is in such a way that I not only want the matching rows, I want the non-match rows as well from the TblStudent.

Basically, I’m saying I want all the students, irrespective of whether they are assigned to a University or not. I want all the Students.

So how do we do that? indirectly you are saying, I want the matching rows, plus the non-matching rows from the TblStudent stable, which means you want everything from the left table matching and non-matching.

if you look at the left join, retains all the matching rows plus non-matching rows from the left table. You know, everything remains exactly similar except that instead of the inner join, you will say 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 excute above query,and see how many records we get. So if you look at the output now, we should have got both Jesiah and Rocky’s records as well.

But if you look at the UniversityName Colum, it’s basically NULL and it makes sense why? Because they don’t have a University_Id they are not assigned a University yet.

So that’s why the University name will be NULL. so left to join basically returns all the matching rows between both the tables, plus non-matching from the left table.

And the picture depicts exactly that scenario. And when you use a left join you can say just left join or you can say left outer join, the query results will not be affected. So the outer keyword is optional there.

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 below image,  you have 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, if somebody asks you, I want all the matching records between both the tables, plus, non-match records from the right table.

so from the TblUniversity table, I want the Massachusetts Institute of Technology (MIT) record as well, irrespective of whether there is a student assigned to that University or not. we want all the rows from the right table.

Let’s see what happens. if we convert this left outer join to 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 we should get around 7 records. Look at the above image with all the matching rows, we’ve also got and Massachusetts Institute of Technology (MIT) rows.

And if you look at the name, gender columns is NULL, because no student is assigned to this University, so obviously you will not have any name, gender.

so whenever you want to return all the matching rows between both the tables that are involved in a join plus non-matching rows from the right table, that’s when we go for 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 the tables, plus non-matching rows from the left table and non-matching from the right table,
then what type of join do I use? full outer join gives me exactly that result.

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

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

 

so far we have seen inner join, left join and right join and outer join, full outer join but apart from all these types of joint, 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 a cross join do and how do I write that query? First, let’s write the query, we look at that output and then we’ll decide what cross join is going to do.

So if it’s a cross join, instead of saying full outer join, all I say is cross join. Now, remember, a cross join will not have an on clause.I’ll tell you 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, look at this, look at the number of rows that we are getting 56 rows
And if you remember, how many rows are there in these tables, 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? The number of rows in the TblStudent table is being multiplied by the number of rows in the TblUniversity table. So across join will give us the Cartesian product of the tables that are involved in the join.

So in summary, cross join returns, the Cartesian products of the tables involved in the join, whereas inner join returns only the matching rows between the tables.

I look at below picture, which will make the understanding of join pretty much easy. so if you look at the inner join, only matching rows, left join 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.

The post What is Sql Joins? With Realtime Examples appeared first on Software Development | Programming Tutorials.