Sql Joins with examples

joins:

1)These are used to retrieve the data from more than one table.

2)To retrieve the data from more than one table the datatypes of fields which related to different tables need not be same while using the joins

Types of joins:

1):Inner Join

2):Cross Join

3)OuterJoin

a)Left Outer Join

b)Right Outer Join

c)Full Outer Join

4)Natural Join

5)Equi Join

Examples and Description:

1:Emp

EmployeeID EmployeeName
1 Ramesh
2 Sukumar
3 Ravi
4 Kalyani

2.Products:

ProductID EmployeeID Productname
1 1 2 Pen
12 3 Pencil
1 2 3 Eraser
1 3 6 Book

1):Inner Join:This join returns all the rows from the both the tables where there is a match.The result set consists of only matched rows.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E inner join Products on E.EmployeeID=P.EmployeeID

Result:

1) EmployeeID EmployeeName Productname
2 Sukumar Pen
3 Ravi Pencil
3 Ravi Eraser

2)Cross Join:Cross join is nothing but retrieving the data from more than one table with out using the condition.

Here two cases are there:

a)select E.EmployeeID,E.EmployeeName,P.Productname from Employees E,Products P

Note:(here we are using the cross join defaultly.Means we have not mentioned the any condition here.)

b)select E.EmployeeID,E.EmployeeName,P.Productname from Employees E cross join Products P

Note:this is the syantax of cross join..both queries(a &b)returns the same result) only the difference is Synatx but the o/p is same.

3)Outer Join:In outer join the resulting table may have empty columns.

a)Left Outer Join:Here left means first table.it reurns all the rows from the first table even though it does not have the matchs in Second table.But it returns only the matched rows from the second table.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E left join Products on E.EmployeeID=P.EmployeeID

Result:

1) EmployeeID EmployeeName Productname
2 Sukumar Pen
3 Ravi Pencil
3 Ravi Eraser

1 Ramesh null

4 Kalyani null

a)Right Outer Join:Here Right means Second table.it returns all the rows from the second table even though it does not have the matchs in First table.But it returns only the matched rows from the First table.

Syntax:

select E. Employeeid,E.EmployeeName,P.ProductName from Employees E right join Products on E.EmployeeID=P.EmployeeID

Result:

1) EmployeeID EmployeeName Productname
2 Sukumar Pen
3 Ravi Pencil
3 Ravi Eraser

6 null Book

5)Natural JOIN:it eliminates the duplicate values from the output.

6)Equi JOIN:An inner join is called equi-join when all the columns are selected with a *, or natural join otherwise