| Just open SQL-Server and start typing the | | | | Department.DepartmentID=Employee.DepartmentID |
| following(for spoon feeding u can also Copy and | | | | /*The above query also fetches the same result as |
| Paste): | | | | Equijoin(or Theta Join) is necessarily similar to Inner Join |
| What are Joins-Â Joins retrieve combination of two | | | | operation but the difference lies in the fact that Inner |
| tables on the basis of a desired match. | | | | Join may be done on the basis of any other predicate |
| Types of Joins- | | | | condition other than '=' |
| - Inner(Equi Join, Natural Join, Cross Join) | | | | Â See tne following Example for this*/select * from |
| - Outer(Left Outer Join, Right Outer Join, Full Outer | | | | Department INNER JOIN Employee |
| Join) | | | | ON |
| - Self Join | | | | Department.DepartmentID>Employee.DepartmentID |
| Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â /* Note: #Join | | | | /*****Natural Join*****/select * from Department |
| implicitly means Inner Join | | | | NATURAL JOIN Employee |
| Â Â #Outer Join must be supplied with either of left, | | | | /*The above query generates same result with the |
| right or full keyword */ | | | | only difference that Natural join gives only one identical |
| Create First Table Department-create table | | | | column*/ |
| Department | | | | Â /*SQL Server doesnot support this syntax it fetches |
| ( | | | | natural join of two tables by using INNER JOIN |
| DepartmentID int PRIMARY KEY, | | | | operation |
| DepartmentName varchar(50) | | | | Â Visit the link: |
| ) | | | | /*****Cross Join*****/select * from Department |
| /* Here DepartmentID will be Primary Key */ | | | | CROSS JOIN Employee |
| Insert some values in Department Table-insert into | | | | Â /*Cross Join gives Cartesian Product -shows |
| Department values(1,'HR')Â insert into Department | | | | records if join condition is true or completely absent*/ |
| values(2,'Admin')insert into Department | | | | Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â /*****OUTER |
| values(3,'Establishment')insert into Department | | | | JOIN*****/ |
| values(4,'SoftwareDevelopment')insert into Department | | | | Â /*Doesnot necessarily contain all the matching |
| values(5,'Clerical') | | | | records each record is retained even if no matching |
| Create Second table Employee-create table | | | | record exists*/ |
| Employee | | | | /*****Left Outer Join*****/select * from Department |
| ( | | | | LEFT OUTER JOIN Employee |
| EmployeeID int PRIMARY KEY IDENTITY, | | | | ON |
| EmployeeName varchar(50), | | | | Â Â Â Â Â |
| DepartmentID int foreign key references | | | | Â /* It gives all records from the first table even if |
| Department(DepartmentID) | | | | there is no match in the second table For instance in |
| ) | | | | this example there  is no Employee working in |
| /* Here DepartmentID is foreign key */ | | | | Department 4 (Software development)*/ |
| Insert some values in EmployeeTable-insert into | | | | Â /*****Right Outer Join*****/select * from Department |
| Employee values('Jazz',2)insert into Employee | | | | RIGHT OUTER JOIN Employee |
| values('Mic',2)insert into Employee values('Joe',3)insert | | | | ON |
| into Employee values('Sam',5)insert into Employee | | | | Â Â Â Â Â Â Â Â Â Â |
| values('Aby',5)insert into Employee values('Jazz',3)insert | | | | Â /*It gives all records from the second table even if |
| into Employee values('Rai',2)insert into Employee | | | | there is no matching record in the first. Here in this |
| values('Tarry',2)insert into Employee | | | | example all |
| values('Shally',1)insert into Employee | | | | Â Employee belong to some or the other Department* |
| values('Akash',2)select * from | | | | select * from Department FULL OUTER JOIN |
| Department,Employeewhere | | | | EmployeeON |
| Department.DepartmentID=Employee.DepartmentID | | | | Department.DepartmentID=Employee.DepartmentID |
| Â /*The above query generates a a table containing all | | | | Â /*It shows all the records from both the tables & |
| the records where DepartmentID in both the    | | | | fills with NULL where no matching occurs*/ |
| tables are same The same result can be obtained | | | |                /*****SELF JOIN*****/ |
| by performing inner join on both the tables*/ | | | | /*Applied to the same table*/select |
| Â Â Â Â Â Â Â Â Â Â Â Â Â /*****INNER JOIN*****/select * | | | | eeName,A.DepartmentIDfrom Employee A,Employee |
| from Department INNER JOIN Employee | | | | Bwhere A.DepartmentID=B.DepartmentID |
| ON | | | | Â /*The above query retrieves combinatios of all |
| Department.DepartmentID=Employee.DepartmentID | | | | Employees working in same Department from the |
| /*****Equi Join*****/select * from Department INNER | | | | Employee table*/ |
| JOIN Employee | | | | Â Â Â Â Â That's all with joins!! |
| ON | | | | |