Joins in Sql Server

Introduction

This is an article about joins in sql server . The article covers all aspects of joins using proper examples and Venn diagrams.

What is meant by joins in SQL server ?

Joins are the commands used to combine data from two or more tables based on relation between them. The relation between them is specified using columns from each tables and relational operators like  =,<,> and <>.

General Syntax : –

Types Of joins in sql server

You can following types of joins in sql server.

  1. Inner Join
  2. Outer Join
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join
  3. Self Join
  4. Cross Join

Before getting into each of them let me introduce table Employee and Job. These two tables will help to discuss each joins with pra.

⌛️ Employee

employeeIdnamesupervisorId
1Steven1
2Nicole1
3Jhon1
4Sarah3

⌛️ Job

jobIdjobNumberemployeeId
1JB1042
2JB1223
3JB3124
4JB4520

Inner Join

Inner join displays only rows that matches the join condition in both tables.

Syntax : –

Instead of INNER JOIN you can use JOIN also both are same.

Venn diagram representation
Inner join in sql server

Illustration :- inner shaded region of Venn diagram represents rows that satisfies joining condition hence it is knows as Inner Join.

Point of Interest (Employee,Job) : – Get Jobs of all employees who has at least one job ?
Ans :

⌛️ Output

namejobNumber
NicoleJB104
JhonJB122
SarahJB312

Left Outer Join

This type of join returns all rows from the left(first) table with  second table rows that matches the join condition. rows without match will have NULL values for second table columns.

Both left outer join and left join are refers exact same operation.

Syntax :-

Venn diagram representation

joins in sql server - left join

 

Point of Interest (Employee,Job) : – Get all employee  with their jobs?

Ans :

⌛️ Output

namejobNumber
StevenNULL
NicoleJB104
JhonJB122
SarahJB312

Right Outer Join

Right join is just an opposite for left join, All rows from right(second) table with matching rows in first table will displayed. If no match found NULL values will be returned for first table columns.

Both Right Outer Join and Right Join are same.

Syntax :-

Venn diagram representation

right join - joins in sql server

Point of Interest (Employee,Job) : – Get all Jobs with employee in charge?

Ans :-

⌛️ Output

namejobNumber
NicoleJB104
JhonJB122
SarahJB312
NULLJB452

Full Outer Join

Full Outer Join returns all rows from both left and right tables. Since it can be treated as combined result of both LEFT and RIGHT joins.

Syntax :-

Venn diagram representation

full join in sql server

Point of Interest (Employee,Job) : – List Out Employee-Job Relation ?

Ans :-

⌛️ Output

namejobNumber
StevenNULL
NicoleJB104
JhonJB122
SarahJB312
NULLJB452

Self Join

Self Join is a type of join where both left and right tables are same table.

Syntax :-

In the syntax, you can see TYPE_OF_JOIN it can be any of the following joins like inner join or outer joins or cross join.
In case of self join you must use alias names for joining tables to differentiate one from another.

Point of Interest (Employee,Job) : where would we use it ?, well consider the following situation.
Query to get details of employee and his supervisor details in one row ?Ans :-

⌛️ Output

EmployeeSupervisor
StevenSteven
NicoleSteven
JhonSteven
SarahJhon

Cross Join (Cartesian Join)

Cross join returns Cartesian product of the tables hence this type of join also knows as Cartesian Join.

Syntax :-

OR

In case of cross join ON clause is not allowed. instead of ON we may use WHERE for additional filter operation.

Visual Representation

cross join - joins in sql server

Consider above two tables Car and Color for explaining cross join, Cross join will return Cartesian product of the tables. Represented as Car X Color

Point of Interest (Car,Color) :- can u picture it’s practical use ?

Q:  Get all the color combination for available cars ?

Ans :

⌛️ Output
Well, you can imagine the output of this cross join, can you guess number of rows returned by this cross join
Yes, it will 16(4*4), Number of rows returned from cross join operation is the product of number of rows in both tables.

This is interesting!😇

If you change join conditions in ON clause as 1=1 for  inner joins and outer join resulting output will be cross join.

Example :

Try it your self.

Post A Reply