Joins in sql server, Inner Join,Cross Join,Left Outer Join,Equi join, Right Outer Join, Full Outer Join (2023)

Introduction:

In this post I will explain what are the Joins in SQL Server and different types of Joins example (SQL LEFT outer Join,SQL RIGHT outer Join,SQL FULL outer Join,SQL Cross Join,SQL inner Join sample,Self Join example) and uses of Joins in SQL Server.

Description:

In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use primary key of first table and foreign key of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table.

Before enter into Joins concept first design two tables in database and enter data like as shown below

Create one table with primary key and give name as “UserDetails”

UserID

UserName

FirstName

LastName

1

SureshDasari

Suresh

Dasari

2

PrasanthiDonthi

Prasanthi

Donthi

3

MaheshDasari

Mahesh

Dasari

Here UserID is the Primary key in UserDetails table

After that create another table with Foreign Key and give name as OrderDetails

OrderID

OrderNo

UserID

1

543224

1

2

213424

2

3

977776

3

4

323233

3

5

998756

1

Here OrderID is the Primary key and UserID is the foreign key in OrderDetails table.

SQL contains different types of Joins we will see each concept with example by using above tables.

Types of Joins

1) Inner Joins

2) Outer Joins

3) Self Join

Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join. This is default join in the query and view Designer.

Syntax for Inner Join

SELECT t1.column_name,t2.column_name

FROM table_name1 t1

INNER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Now check the below query for inner join

Example

SELECT u.UserName,u.LastName,o.OrderNo

FROM UserDetails u

INNER JOIN OrderDetails o

ON u.UserID=o.UserID

Once we run that query our output will be like this

(Video) Inner Join, Left Join, Right Join and Full Outer Join in SQL Server | SQL Server Joins

UserName

LastName

OrderNo

SureshDasari

Dasari

543224

PrasanthiDonthi

Donthi

213424

MaheshDasari

Dasari

977776

MaheshDasari

Dasari

323233

SureshDasari

Dasari

998756

We can write our inner join query like this also it will give same result

SELECT u.UserName,u.LastName,o.OrderNo

FROM UserDetails u

JOIN OrderDetails o

ON u.UserID=o.UserID

Based on above result we can say that INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in "UserDetails" that do not have matches in "OrderDetails", those rows will NOT be listed.

In inner Join we are having different types of Joins those are

1) Equi Join

2 2) Natural Join

3) Cross Join

Equi Join

The Equi join is used to display all the matched records from the joined tables and also display redundant values. In this join we need to use * sign to join the table.

Syntax for Equi Join

SELECT * FROM table_name1 t1

INNER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Now check the below query for Equi join

Example

SELECT *

FROM UserDetails u

INNER JOIN OrderDetails o

ON u.UserID=o.UserID

Once we run above query our output will be like this

UserID

UserName

FirstName

LastName

OrderID

OrderNo

UserID

1

SureshDasari

Suresh

Dasari

1

543224

1

2

PrasanthiDonthi

Prasanthi

Donthi

2

213424

2

3

MaheshDasari

Mahesh

Dasari

3

977776

3

3

MaheshDasari

Mahesh

Dasari

4

323233

3

1

SureshDasari

Suresh

Dasari

5

998756

1

In equi join we need to use only equality comparisons in the join relation. If we use other operators such as (<,>) for our comparison condition then our Joins disqualifies for equi join.

Natural Joins

The Natural join is same as our Equi join but only the difference is it will restrict to display redundant values.

(Video) SQL Server joins :- Inner join,Left join,Right join ,full outer join and cross join

Syntax for Natural Join

SELECT * FROM table_name1 t1

NATURAL JOIN table_name2 t2

Example

SELECT *

FROM UserDetails

NATURAL JOIN OrderDetails

Note: These NATURAL Joins won’t work in our SQL Server (only supports in Oracle) it will throw syntax error. If you observe above code "NATURAL" is not highlighted, indicating that it is not recognized as a keyword.

Cross Join

A cross join that produces Cartesian product of the tables that involved in the join. The size of a Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table.

Syntax for Cross Join

SELECT * FROM table_name1

CROSS JOIN table_name2

Or we can write it in another way also

SELECT * FROM table_name1,table_name2

Now check the below query for Cross join

Example

SELECT * FROM UserDetails

CROSS JOIN OrderDetails

Or

SELECT * FROM UserDetails, OrderDetails

Once we run that query our output will be like this

UserID

UserName

FirstName

LastName

OrderID

OrderNo

UserID

1

SureshDasari

Suresh

Dasari

1

543224

1

1

SureshDasari

Suresh

Dasari

2

213424

2

1

SureshDasari

Suresh

Dasari

3

977776

3

1

SureshDasari

Suresh

Dasari

4

323233

3

1

SureshDasari

Suresh

Dasari

5

998756

1

2

PrasanthiDonthi

Prasanthi

Donthi

1

543224

1

2

PrasanthiDonthi

Prasanthi

Donthi

2

213424

2

2

PrasanthiDonthi

Prasanthi

Donthi

3

977776

3

2

PrasanthiDonthi

Prasanthi

Donthi

4

323233

3

2

PrasanthiDonthi

Prasanthi

Donthi

5

998756

1

3

MaheshDasari

Mahesh

Dasari

1

543224

1

3

MaheshDasari

Mahesh

Dasari

2

213424

2

3

MaheshDasari

Mahesh

Dasari

3

977776

3

3

MaheshDasari

Mahesh

Dasari

4

323233

3

3

MaheshDasari

Mahesh

Dasari

5

998756

1

Outer Joins

A join that return all the rows that satisfy the condition and unmatched rows in the joined table is an Outer Join.

(Video) Different types of JOINs in SQL Server - INNER, LEFT, RIGHT, CROSS and FULL JOINs.

We are having three types of Outer Joins

Left Outer Join

Right Outer Join

Full Outer Join

Left Outer Join

The left outer join displays all the rows from the first table and matched rows from the second table.

Syntax for Left Outer Join

SELECT Column_List FROM table_name1 t1

LEFT OUTER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Now check the below query for Left Outer join

Example

SELECT u.UserID,u.UserName,o.OrderNo

FROM UserDetails u

LEFT OUTER JOIN OrderDetails o

ON u.UserID=o.UserID

Once we run that query our output will be like this

UserID

UserName

OrderNo

1

SureshDasari

543224

1

SureshDasari

998756

2

PrasanthiDonthi

213424

3

MaheshDasari

977776

3

MaheshDasari

323233

Right Outer Join

The right outer join displays all the rows from the second table and matched rows from the first table.

Syntax for Right Outer Join

SELECT Column_List FROM table_name1 t1

RIGHT OUTER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Now check the below query for Right Outer join

Example

SELECT u.UserID,u.UserName,o.OrderNo

(Video) SQL Joins - How to use Inner Join, Outer Join, Self Join

FROM UserDetails u

RIGHT OUTER JOIN OrderDetails o

ON u.UserID=o.UserID

Once we run that query our output will be like this

UserID

UserName

OrderNo

1

SureshDasari

543224

2

PrasanthiDonthi

213424

3

MaheshDasari

977776

3

MaheshDasari

323233

1

SureshDasari

998756

Full Outer Join

Full Outer Join displays all the matching and non matching rows of both the tables.

Syntax for Full Outer Join

SELECT Column_List FROM table_name1 t1

FULL OUTER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Now check the below query for Full Outer join

Example

SELECT u.UserID,u.UserName,o.OrderNo

FROM UserDetails u

FULL OUTER JOIN OrderDetails o

ON u.UserID=o.UserID

Once we run that query our output will be like this

UserID

UserName

FirstName

LastName

OrderID

OrderNo

UserID

1

SureshDasari

Suresh

Dasari

1

543224

1

1

SureshDasari

Suresh

Dasari

5

998756

1

2

PrasanthiDonthi

Prasanthi

Donthi

2

213424

2

3

MaheshDasari

Mahesh

Dasari

3

977776

3

3

MaheshDasari

Mahesh

Dasari

4

323233

3

Self Join

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

To implement self join first design table and give a name as “EmployeeDetails

EmpID

EmpName

EmpMgrID

1

Suresh

2

2

Prasanthi

4

3

Mahesh

2

4

Sai

1

5

Nagaraju

1

6

Mahendra

3

7

Sanjay

3

Now I want to get manager names of particular employee for that we need to write query like this

select e2.EmpName,e1.EmpName as 'Manager'

from EmployeeDetails e1

INNER JOIN EmployeeDetails e2

on e1.EmpID=e2.EmpMgrID

Here if you observe above query EmployeeDetails table joined itself using table aliases e1 and e2.

After that run our query output will be like this

(Video) SQL Outer Joins and Cross Joins Tutorial

EmpName

Manger

Sai

Suresh

Nagaraju

Suresh

Suresh

Prasanthi

Mahesh

Prasanthi

Mahendra

Mahesh

Sanjay

Mahesh

Prasanthi

Sai

Videos

1. Joins and types of join-cross join,equi or inner join ,self join,outer join and Alias Name in SQL
(VTS TECHNOCRATS )
2. SQL - Part 59 - Joins (Inner Join, Left Join, Right Join, Full Join and Self Join)
(QAFox)
3. Joins in sql server - Part 12
(kudvenkat)
4. SQL Joins Explained |¦| Joins in SQL |¦| SQL Tutorial
(Socratica)
5. Tutorial#67 Full outer Join in Oracle SQL Database
(EqualConnect Coach)
6. SQL Server 2014 Join
(Mayank Sanghvi)
Top Articles
Latest Posts
Article information

Author: Gov. Deandrea McKenzie

Last Updated: 12/31/2022

Views: 5745

Rating: 4.6 / 5 (46 voted)

Reviews: 93% of readers found this page helpful

Author information

Name: Gov. Deandrea McKenzie

Birthday: 2001-01-17

Address: Suite 769 2454 Marsha Coves, Debbieton, MS 95002

Phone: +813077629322

Job: Real-Estate Executive

Hobby: Archery, Metal detecting, Kitesurfing, Genealogy, Kitesurfing, Calligraphy, Roller skating

Introduction: My name is Gov. Deandrea McKenzie, I am a spotless, clean, glamorous, sparkling, adventurous, nice, brainy person who loves writing and wants to share my knowledge and understanding with you.