Join Types in SQL Server

dbtut

6 years ago

We can combine more than one table through equi-columns in tables and perform joins in a single result set.

There are a few kinds of join types. The result set will be changed according to the Join type used.

Let’s go over an example to understand the subject clearly.

The following script will create two tables and add some records into it.

One of the tables will be a “City” table and city names will be listed. The other table will include the city’s famous food.

CREATE TABLE City(
    ID int,
    CityName varchar(100)
)

CREATE TABLE FamousFood(
    ID int,
    CityID int,
    FoodName varchar(100)
)

USE [Test]
GO
INSERT INTO [dbo].[City]([ID],[CityName ])VALUES    (1,'Newyork')
INSERT INTO [dbo].[City]([ID],[CityName ])VALUES    (42,'Roma')
INSERT INTO [dbo].[City]([ID],[CityName ])VALUES    (3,'Paris')
GO
INSERT INTO [dbo].[FamousFood]([ID],[CityID ],[FoodName ])VALUES    (1,1,'x')
INSERT INTO [dbo].[FamousFood]([ID],[CityID ],[FoodName ])VALUES    (2,42,'y')
INSERT INTO [dbo].[FamousFood]([ID],[CityID ],[FoodName ])VALUES    (3,3,'z')

CREATE TABLE City(

ID int,

CityName varchar(100)

)

CREATE TABLE FamousFood(

ID int,

CityID int,

FoodName varchar(100)

)

USE [Test]

INSERT INTO [dbo].[City]([ID],[CityName ])VALUES (1,'Newyork')

INSERT INTO [dbo].[City]([ID],[CityName ])VALUES (42,'Roma')

INSERT INTO [dbo].[City]([ID],[CityName ])VALUES (3,'Paris')

INSERT INTO [dbo].[FamousFood]([ID],[CityID ],[FoodName ])VALUES (1,1,'x')

INSERT INTO [dbo].[FamousFood]([ID],[CityID ],[FoodName ])VALUES (2,42,'y')

INSERT INTO [dbo].[FamousFood]([ID],[CityID ],[FoodName ])VALUES (3,3,'z')

As you can see in the Script, there is a column named “CityID” in the “FamousFood” table.

The “CityID” column in the “FamousFood” table and the “ID” column in the “City” table are related.

We will use these two columns to join these two tables. When writing scripts with TSQL, we can use the following join types.

JOIN or INNER JOIN (both mean the same thing)
OUTER JOIN
LEFT JOIN or LEFT OUTER JOIN (both mean the same thing)
RIGHT JOIN or RIGHT OUTER JOIN (both mean the same thing)
FULL JOIN or FULL OUTER JOIN (both mean the same thing)
CROSS JOIN

The following screenshot makes it easier to understand how join types join tables.

You can find the details of the join types I have listed below.

JOIN or INNER JOIN in SQL Server

This type of join joins the common records of the two tables.

We can join two tables we created at the beginning of the article with inner join as follows.

By joining two table with the “ID” column in the “City” table and the “CityID” column in the “FamousFood” table, we will obtain a result set that shows which city has which famous food.

select * from City
GO
select * from FamousFood
GO
select s.CityName,my.FoodName from City s
INNER JOIN FamousFood my ON s.ID=my.CityID

select * from City

select * from FamousFood

select s.CityName,my.FoodName from City s

INNER JOIN FamousFood my ON s.ID=my.CityID

If we add a record to the “City” table and If we do not add the famous food of this city to the “FamousFood” table, this record will not be in the result set of the join process.

To test, we will add a record to the “City” table, but we will not add the famous food of the city to the “FamousFood” table.

INSERT INTO [dbo].[City]([ID],[CityName])VALUES(27,'Hongkong')

1	INSERT INTO [dbo].[City]([ID],[CityName])VALUES(27,'Hongkong')

When we re-run the INNER JOIN script that we just ran, we will not see a record of Hongkong as you see.

SQL Server Aliases

The “s” at the end of the city table and the “my” at the end of the “FamousFood” table are called aliases.

This way you can write your scripts more clearly.

As you can see in the script below, if we have defined an alias, we need to use the alias to select the columns we need.

Another goal of Alias is that if two tables have columns with the same name and the name of the column is entered directly in select without specifying the alias, you will get an error like this:

The error we get when we try to select the ID column with the same name in our query without Alias:

select ID,CityName,FoodName from City s
INNER JOIN FamousFood my  ON s.ID=my.CityID

1 2	select ID,CityName,FoodName from City s INNER JOIN FamousFood my ON s.ID=my.CityID

Msg 209, Level 16, State 1, Line 1

Ambiguous column name ‘ID’.

OUTER JOIN in SQL Server

Like INNER JOIN, it uses common columns between tables to join common records.

Unlike INNER JOIN, non-matching rows also can be in result set. You can not use the OUTER JOIN alone by typing OUTER JOIN.

You can use OUTER JOIN as LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN.

LEFT JOIN or LEFT OUTER JOIN in SQL Server(both mean the same thing)

The records in the first table are fully returns in the result set. If the second table does not have the counterpart of the record in the first table, the value in the second table returns null.

For example, in the example we wrote an INNER JOIN, we inserted Hongkong in the “City” table.

But since we did not insert a record about Hongkong in the “FamousFood” table, it was not listed in INNER JOIN.

Let’s run the same query by typing LEFT JOIN or LEFT OUTER JOIN.

select s.CityName,my.FoodName from City s
LEFT OUTER JOIN FamousFood my ON s.ID=my.CityID

1 2	select s.CityName,my.FoodName from City s LEFT OUTER JOIN FamousFood my ON s.ID=my.CityID

As you can see below, the value of Hongkong returned in the result set and the “FoodName” column in the “FamousFood” table was null because there was no record.

You can meet your different needs by filtering this query in the where condition.

For example, if we want to list cities which has not famous food, we can write the following query.

select s.CityName,my.FoodName from City s
LEFT OUTER JOIN FamousFood my ON s.ID=my.CityID
where my.CityID is null

select s.CityName,my.FoodName from City s

LEFT OUTER JOIN FamousFood my ON s.ID=my.CityID

where my.CityID is null

RIGHT JOIN or RIGHT OUTER JOIN in SQL Server(both mean the same thing)

It works the same logic as LEFT OUTER JOIN. This time, all the records in the second table returns.

Records that do not correspond in the first table are returned as null.

Let’s run our query again by turning the query with LEFT OUTER JOIN to RIGHT OUTER JOIN.

select s.CityName,my.FoodName from City s
RIGHT OUTER JOIN FamousFood my ON s.ID=my.CityID

1 2	select s.CityName,my.FoodName from City s RIGHT OUTER JOIN FamousFood my ON s.ID=my.CityID

As you can see in the screenshot below, there is no record of null for “CityName”.

Because all of the values in the “CityID” column in the “FamousFood” table are in the “ID” column of the “City” table.

I will add a record to the “FamousFood” table with the following script.

And let’s say this record is 16 for “CityID”. The record we will insert will be Bursa’s Iskender.

But we will not add Bursa to the “City” table.

INSERT INTO [dbo].[FamousFood]([ID],[CityID],[FoodName])VALUES    (4,16,'Iskender')

1	INSERT INTO [dbo].[FamousFood]([ID],[CityID],[FoodName])VALUES (4,16,'Iskender')

Let’s run our RIGHT OUTER JOIN script once we’ve done the Insert operation.

We see the value of Iskender as you see below.

However, since there is no City associated with Iskender on the “City” table, the “CityName” column is null.

FULL JOIN or FULL OUTER JOIN in SQL Server(both mean the same thing)

All records in the two tables are returns. But the columns that do not correspond returns empty.

Let’s run the same script with FULL OUTER JOIN.

select s.CityName,my.FoodName from City s
FULL OUTER JOIN FamousFood my ON s.ID=my.CityID

1 2	select s.CityName,my.FoodName from City s FULL OUTER JOIN FamousFood my ON s.ID=my.CityID

As you see below,

The value of Hongkong came, but the food came null

The value of Iskender came, but the city came null

CROSS JOIN in SQL Server

Gives cartesian multiplication of tables.

That is, it returns all rows of the table on the right corresponding to each row in the table on the left.

I think you can understand better by writing your query and looking at the result in the following way.

select s.CityName,my.FoodName from City s
CROSS JOIN FamousFood my

1 2	select s.CityName,my.FoodName from City s CROSS JOIN FamousFood my

If we write the expression we write in the ON statement in the INNER JOIN, in the where block of the CROSS JOIN, it will work like an INNER JOIN.

When you run the following script, you will see that the CROSS JOIN with the WHERE appended, returns the same result as the INNER JOIN.

select s.CityName,my.FoodName from City s
CROSS JOIN FamousFood my
where s.ID=my.CityID

select s.CityName,my.FoodName from City s

CROSS JOIN FamousFood my

where s.ID=my.CityID

After this article, you may want to read the article titled “JOIN Types in SQL Server Execution Plan“.