Joins

 1. Self-join (with in a table if you want to join)

 2. Inner join (join more than one table will get matching records)

 3. Left join

 4. Right join

 5. Cross join

--self Join

Select e1.EmpID,e1.Name,e1.Address from Employee e1

join Employee e2 on e1.Address = e2.Address and e1.EmpID<>e2.EmpID

-- Inner Join

Select EmpID,Name,Address,Salary,

dept.DeptName,dept.LOcation,Dept.EMPID_FK from Employee emp

join Department dept on emp.EmpID = dept.EmpID_FK

or

Select EmpID,Name,Address,Salary,

dept.DeptName,dept.LOcation,Dept.EMPID_FK from Employee emp

inner join Department dept on emp.EmpID = dept.EmpID_FK

-- Left Join

Select EmpID,Name,Address,Salary,

dept.DeptName,dept.LOcation,Dept.EMPID_FK from Employee emp

Left join Department dept on emp.EmpID = dept.EmpID_FK

-- Right Join

Select EmpID,Name,Address,Salary,

dept.DeptName,dept.LOcation,Dept.EMPID_FK from Employee emp

Right join Department dept on emp.EmpID = dept.EmpID_FK

-- CROSS Join

Select EmpID,Name,Address,Salary,

dept.DeptName,dept.LOcation,Dept.deptID from Employee emp

cross join Department dept

-- n * n

-- first table rows*second table rows

Indexes in SQL

To speed up the performance while retrieving the data

1.       Clusted Index

When you create primary in a table and you can create only one clustered index per table.

2.       NonClustered Index
create index loc_Search on Department(LOcation)

View

Logical representation of table

create view vw_myEmpSearch

AS

Select EmpID,Name,Address,Salary from Employee

select * from vw_myEmpSearch