Join in sql server

Join is used to retrieve data from two or more table, Table are related to each other through foreign key

There are three types of join in SQL Server

· Inner join:- retrieve value when column match from both table.

· Outer join:-

· Cross join

· Left:-return all matching row+non matching row of left.

· Right:-return all matching row+non matching row of right.

· Full:-return all record from both matching table including none matching.

1. select * from

Table 1 left outer join or right outer join

Table2 on join condition

2. select * from

Table 1 full outer join table 2 on condition

3. select * from

Table 1 full join table 2 on condition

Produce the Cartesian product of two table .every record of one table multiply with record of other table.

Cross join shouldn't have on.

Syntax of Inner join

Select * from

Table 1 join or inner join (intersection)

table2

On join condition

Retrieve only non-matching row from left table

Select column name from table1 t1 left join table2 on t1.id=t2.id where t2.id is null

Retrieve only non-matching row from right table

Select column name from table t1 left join table t2 on t1.id=t2.id where t1.id is null

Retrieve only non-matching row from both table

Select column name from table t1 full join table t2 on t1.id=t2.id where t1.id is null or t2.id is null