Just open SQL-Server and start typing the following(for spoon feeding u can also Copy and Paste):

What are Joins- Joins retrieve combination of two tables on the basis of a desired match.

Types of Joins-

• Inner(Equi Join, Natural Join, Cross Join)
  


• Outer(Left Outer Join, Right Outer Join, Full Outer Join)
  


• Self Join
  

/* Note: #Join implicitly means Inner Join

#Outer Join must be supplied with either of left, right or full keyword */

Create First Table Department-

create table Department

(

DepartmentID int PRIMARY KEY,

DepartmentName varchar(50)

)

/* Here DepartmentID will be Primary Key */

Insert some values in Department Table-

insert into Department values(1,'HR')

insert into Department values(2,'Admin')

insert into Department values(3,'Establishment')

insert into Department values(4,'SoftwareDevelopment')

insert into Department values(5,'Clerical')

Create Second table Employee-

create table Employee

(

EmployeeID int PRIMARY KEY IDENTITY,

EmployeeName varchar(50),

DepartmentID int foreign key references Department(DepartmentID)

)

/* Here DepartmentID is foreign key */

Insert some values in EmployeeTable-

insert into Employee values('Jazz',2)

insert into Employee values('Mic',2)

insert into Employee values('Joe',3)

insert into Employee values('Sam',5)

insert into Employee values('Aby',5)

insert into Employee values('Jazz',3)

insert into Employee values('Rai',2)

insert into Employee values('Tarry',2)

insert into Employee values('Shally',1)

insert into Employee values('Akash',2)

select * from Department,Employee

where Department.DepartmentID=Employee.DepartmentID

/*The above query generates a a table containing all the records where DepartmentID in both the tables are same The same result can be obtained by performing inner join on both the tables*/

/*****INNER JOIN*****/

select * from Department INNER JOIN EmployeeON Department.DepartmentID=Employee.DepartmentID

/*****Equi Join*****/

select * from Department INNER JOIN Employee

ON Department.DepartmentID=Employee.DepartmentID

/*The above query also fetches the same result as Equijoin(or Theta Join) is necessarily similar to Inner Join operation but the difference lies in the fact that Inner Join may be done on the basis of any other predicate condition other than '='

See tne following Example for this*/

select * from Department INNER JOIN Employee

ON Department.DepartmentID>Employee.DepartmentID

/*****Natural Join*****/

select * from Department NATURAL JOIN Employee

/*The above query generates same result with the only difference that Natural join gives only one identical column*/

/*SQL Server doesnot support this syntax it fetches natural join of two tables by using INNER JOIN operation

Visit the link: http://blog.sqlauthority.com/...7/sql-server-get-common-records-from-two-tables-without-using-join*/

/*****Cross Join*****/

select * from Department CROSS JOIN Employee /*Cross Join gives Cartesian Product -shows records if join condition is true or completely absent*/

/*****OUTER JOIN*****/

/*Doesnot necessarily contain all the matching records each record is retained even if no matching record exists*/

/*****Left Outer Join*****/

select * from Department LEFT OUTER JOIN EmployeeON Department.DepartmentID=Employee.DepartmentID /* It gives all records from the first table even if there is no match in the second table For instance in this example there is no Employee working in Department 4 (Software development)*/

/*****Right Outer Join*****/

select * from Department RIGHT OUTER JOIN Employee

ON Department.DepartmentID=Employee.DepartmentID

/*It gives all records from the second table even if there is no matching record in the first. Here in this example all Employee belong to some or the other Department*/select * from Department FULL OUTER JOIN EmployeeON Department.DepartmentID=Employee.DepartmentID /*It shows all the records from both the tables & fills with NULL where no matching occurs*/

/*****SELF JOIN*****/

/*Applied to the same table*/

select A.EmployeeID,A.EmployeeName,B.EmployeeID,B.EmployeeName,A.DepartmentID

from Employee A,Employee B

where A.DepartmentID=B.DepartmentID

/*The above query retrieves combinatios of all Employees working in same Department from the Employee table*/

That's all with joins!!

Shees Abidi,