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.

⌛️ Employee

employeeId	name	supervisorId
1	Steven	1
2	Nicole	1
3	Jhon	1
4	Sarah	3

⌛️ Job

jobId	jobNumber	employeeId
1	JB104	2
2	JB122	3
3	JB312	4
4	JB452	0

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

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

name	jobNumber
Nicole	JB104
Jhon	JB122
Sarah	JB312

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

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

Ans :

⌛️ Output

name	jobNumber
Steven	NULL
Nicole	JB104
Jhon	JB122
Sarah	JB312

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

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

Ans :-

⌛️ Output

name	jobNumber
Nicole	JB104
Jhon	JB122
Sarah	JB312
NULL	JB452

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

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

Ans :-

⌛️ Output

name	jobNumber
Steven	NULL
Nicole	JB104
Jhon	JB122
Sarah	JB312
NULL	JB452

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

Employee	Supervisor
Steven	Steven
Nicole	Steven
Jhon	Steven
Sarah	Jhon

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

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.