Joins

In the Current Blog I will Talk about joins

what are joins ?
Types of Joins ?

So lets get started

Join is keyword in SQL Server, which helps to join records from more than one table

Their are 6 types of joins

• Inner join
• Left Join  also called Left outer Join
• Right join also called right outer Join
• Full outer join
• self join
• cross join / Cartesian product join

Inner join : Inner join gets the records from both the tables which has common elements
 syntax   Select a.column1, a.column2, b.column3
               From Tablea as a
               Inner join Tableb  as b on a.column1 = b.column4

Example

Employee

Emp_ID	Emp_name	Emp_salary	Dept_ID
1	Sandeep	1200	10
2	Alan	1300	20
3	Kalid	480	30
4	David	678	60
5	Pradeep	899	10
6	Javid	2300	40

Dept_ID	Dept_name
10	Computer Science
20	Agricultural Science
30	Commercial Studies
40	Technical Drawing
50	Mathematic

Select e.* ,d.Dept_name
From dbo.Employee e
inner join dbo.Dept d on e.dept_ID = d.Dept_ID

Emp_Id	Emp_name	Emp_salary	dept_ID	Dept_name
1	sandeep	1200	10	Computer Science
5	pradeep	899	10	Computer Science
2	Alan	1300	20	Agricultural Science
3	Kalid	480	30	Commercial Studies
6	Javid	2300	40	Technical Drawing

Left join/Left outer join : Left join will write down whole left table and the matching information from the right table
and not matching records will have null's joined

Select e.* ,d.Dept_name
From dbo.Employee e
Left  join dbo.Dept d on e.dept_ID = d.Dept_ID

Emp_Id	Emp_name	Emp_salary	dept_ID	Dept_name
1	sandeep	1200	10	Computer Science
2	Alan	1300	20	Agricultural Science
3	Kalid	480	30	Commercial Studies
4	David	678	60	NULL
5	pradeep	899	10	Computer Science
6	Javid	2300	40	Technical Drawing

Right join/Right outer join : Right join will write down whole right table and the matching information from the Left table and not matching records will have null's joined

Example

Select e.* ,d.Dept_name

From dbo.Employee e

Right  join dbo.Dept d on e.dept_ID = d.Dept_ID

Emp_Id	Emp_name	Emp_salary	dept_ID	Dept_name
1	sandeep	1200	10	Computer Science
5	pradeep	899	10	Computer Science
2	Alan	1300	20	Agricultural Science
3	Kalid	480	30	Commercial Studies
6	Javid	2300	40	Technical Drawing
NULL	NULL	NULL	NULL	Mathematics

Full Outer join :  Full outer join is the combination of left join and right join  with distinct information 

Example

Select e.* ,d.Dept_name

From dbo.Employee e

Full outer  join dbo.Dept d on e.dept_ID = d.Dept_ID

Emp_Id	Emp_name	Emp_salary	dept_ID	Dept_name
1	sandeep	1200	10	Computer Science
2	Alan	1300	20	Agricultural Science
3	Kalid	480	30	Commercial Studies
4	David	678	60	NULL
5	pradeep	899	10	Computer Science
6	Javid	2300	40	Technical Drawing
NULL	NULL	NULL	NULL	Mathematics

Self Join : Self Join is joining of table by itself  

Example

Emp_Id	Emp_name	Emp_salary	dept_ID	Manager_ID
1	sandeep	1200	10	2
2	Alan	1300	20	4
3	Kalid	480	30	6
4	David	678	60	2
5	pradeep	899	10	4
6	Javid	2300	40	6

Select e.* ,e1.Emp_name as manager_name

From dbo.Employee e ,dbo.Employee e1

where e.Manager_id = e1.Emp_Id

Emp_Id	Emp_name	Emp_salary	dept_ID	Manager_ID	manager_name
1	sandeep	1200	10	2	Alan
4	David	678	60	2	Alan
2	Alan	1300	20	4	David
5	pradeep	899	10	4	David
3	Kalid	480	30	6	Javid
6	Javid	2300	40	6	Javid

Cross join/Cartesian product join : cross join is a kind of join that would get all possible combinations of rows as the result set from tables

In other words its the multiplication of rows from both the tables 

Example

 First table has 6 rows and second table has 5 rows 

Emp_Id	Emp_name	Emp_salary	dept_ID	Manager_ID	Dept_ID	Dept_name
1	sandeep	1200	10	2	10	Computer Science
2	Alan	1300	20	4	10	Computer Science
3	Kalid	480	30	6	10	Computer Science
4	David	678	60	2	10	Computer Science
5	pradeep	899	10	4	10	Computer Science
6	Javid	2300	40	6	10	Computer Science
1	sandeep	1200	10	2	20	Agricultural Science
2	Alan	1300	20	4	20	Agricultural Science
3	Kalid	480	30	6	20	Agricultural Science
4	David	678	60	2	20	Agricultural Science
5	pradeep	899	10	4	20	Agricultural Science
6	Javid	2300	40	6	20	Agricultural Science
1	sandeep	1200	10	2	30	Commercial Studies
2	Alan	1300	20	4	30	Commercial Studies
3	Kalid	480	30	6	30	Commercial Studies
4	David	678	60	2	30	Commercial Studies
5	pradeep	899	10	4	30	Commercial Studies
6	Javid	2300	40	6	30	Commercial Studies
1	sandeep	1200	10	2	40	Technical Drawing
2	Alan	1300	20	4	40	Technical Drawing
3	Kalid	480	30	6	40	Technical Drawing
4	David	678	60	2	40	Technical Drawing
5	pradeep	899	10	4	40	Technical Drawing
6	Javid	2300	40	6	40	Technical Drawing
1	sandeep	1200	10	2	50	Mathematics
2	Alan	1300	20	4	50	Mathematics
3	Kalid	480	30	6	50	Mathematics
4	David	678	60	2	50	Mathematics
5	pradeep	899	10	4	50	Mathematics
6	Javid	2300	40	6	50	Mathematics