Basic information about Joins in Sql server

Joins are really important for writing queries in any database language. If you take SQL Server there are few joins available which can help to construct sql statemetns. The Joins are combination of keywords Intersect, Union, Union all which we have learnt during our X standards. Join can be matched to 2 or more tables (usually), you can match single table also using join i.e., called Self join.

In Sql server we have mainly
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join
Self Join

For best understanding create following tables and insert the data in sample database. So that you can understand very easily how the join condition results.

----------------------------
Create table Table1 (id int, name varchar(10))
Create table Table2 (id int, Name varchar(10))

insert into table1 (id, name)
values (1,'A')
insert into table1 (id, name)
values (2,'AB')
insert into table1 (id, name)
values (3,'ABC')

insert into table2 (id, name)
values (1,'A')
insert into table2 (id, name)
values (2,'AB')

----------------------------
JOINS :
Inner join : A Intersect B; it means the data results which are available in both the tables.
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A inner join Table2 B on a.id = b.id

Left outer Join : A union B : Whatever the data available in A table and the matching data from B Table
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Left outer join Table2 B on a.id = b.id

Right outer Join : A union B : Whatever the data available in B table and the matching data from A Table
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Right outer join Table2 B on a.id = b.id

Full outer Join : All the data from A and B tables
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Full outer join Table2 B on a.id = b.id

Cross Join : A X B ; for each row in a multiply into all rows in B. Like wise for all the rows.
SELECT a.id, a.Name,b.id, b.Name from
Table1 A cross join Table2 B

Self Join : By using self join we can match the same table for different columns.
SELECT a.id, a.Name,b.id, b.Name from
Table1 A join Table1 B on a.id = b.id