0
25kviews
What is JOIN? Explain different types of join with example
1 Answer
2
626views

This type of join is useful when we know the joining conditions.

Syntax:

SELECT Column_list

FROM Table1, Table 2

WHERE Join_Condition

Types of Join:

1.Natural Join

• A natural join returns all rows by matching values in comman columns having same name and data types of columns and that column should be present in both tables.

• Natural join eliminates duplicate columns present in JOIN table. Therefore comman column will be printed only once in resultant table.

Syntax:

SELECT Column_List

FROM Table1

NATURAL JOIN

Table 2

Employee Table

Eid EName Did
1 Mahesh 100
2 Suhas 200
3 Jayendra 300

Department Table

Did Dname
100 HR
200 TIS

SELECT E.Eid[Eid],

D.Did[Did]

D.Dname[Dname]

FROM Employee E

NATURAL JOIN

Department D

Eid Did DName
1 100 HR
2 200 TIS
3 300 HR

2.Cartesian Product/CROSS JOIN

• CROSS join occur due to WHERE condition is missing in query or some invalid operations in where clause leads to undesired results or CROSS Join.

Syntax:

SELECT Column_List

FROM Table1

CROSS JOIN

Table 2

Employee Table

Eid EName Did
1 Mahesh 100
2 Suhas 200
3 Jayendra 300

Department Table

Did DName
100 HR
200 TIS

SELECT e.Eid[Eid],

e.Ename[Ename]

e.Did[Did]

d.Did[Did]

d.Dname[Dname]

FROM Employee e

CROSS JOIN

Department d

Eid EName Did Did Dname
1 Mahesh 100 100 HR
1 Mahesh 100 200 TIS
2 Suhas 200 100 HR
2 Suhas 200 200 TIS
3 Jayendra 100 100 HR
3 Jayendra 100 200 TIS

3.Self Join

•Any table can be joined by itself as long as aeachtable reference is given different name using table alias.

Example:

Employee Table (E)

Eid Ename Mid
1 Mahesh 2
2 Suhas 3
3 Jayendra 3

Manager Table(M)

Eid Ename Mid
1 Mahesh 2
2 Suhas 3
3 Jayendra 3

SELECT E.Ename[Employee],

M.Ename[Manager]

FROM Employee E

CROSS

EmployeeM

ON E.Mid=M.Eid

Employee Manager
Mahesh Suhas
Suhas Jayendra
Jayendra Jayendra

4.Inner Join

•Inner Join joins two table when there is atleast one match between two tables.

Syntax:

SELECT Column_List

FROM Table1

INNER JOIN

Table 2

ON (JOIN_Condition)

Employee Table

Eid EName Did
1 Mahesh 100
2 Suhas 200
3 Jayendra 300

Department Table

Did DName
50 DEV
100 HR
200 TIS

SELECT E.Eid[Eid],

D.Did[Did]

D.Dname[Dname]

FROM Employee E

INNER JOIN

Department D

ON E.Did=D.DeptId

Eid Did Dname
1 100 HR
2 200 TIS
3 300 HR

5. Outer Join

•The join that can be used to see rows of tables that do not meet the join condition along with rows that satisfies join condition is called as Outer Join.

a)Left Outer Join:

•A left outer join returns all the rows for which the join condition is true and returns all other rows from the dominant table and displays the corresponding values from the subordinate table as NULL.

Employee Table

Eid EName Did
1 Mahesh 100
2 Suhas 200
3 Jayendra 500

Department Table

Did DName
50 DEV
100 HR
200 TIS

SELECT E.Eid[Eid],

D.Did[Did]

D.Dname[Dname]

FROM Employee E

LEFT OUTER JOIN

Department D

ON E.Did=D.DeptId

Eid Did Dname
1 100 HR
2 200 TIS
3 500 NULL

a) Right Outer Join:

• A right outer join returns all the rows for which the join condition is true and returns all other rows from the dominant table and displays the corresponding values from the subordinate table as NULL.

Employee Table

Eid EName Did
1 Mahesh 100
2 Suhas 200
3 Jayendra 500

Department Table

Did DName
50 DEV
100 HR
200 TIS

SELECT E.Eid[Eid],

D.Did[Did]

D.Dname[Dname]

FROM Employee E

RIGHT OUTER JOIN

Department D

ON E.Did=D.DeptId

Eid Did Dname
1 100 HR
2 200 TIS
NULL 50 DEV

c)Full Outer Join:

A full outer join returns all the rows for which the join condition is true and returns

i)All other rows from the right table and displays the corresponding values from the left table as NULL.

ii)All other rows from the left table and displays the corresponding values from the right table as NULL.

Employee Table

Eid EName Did
1 Mahesh 100
2 Suhas 200
3 Jayendra 500

Department Table

Did DName
50 DEV
100 HR
200 TIS

SELECT E.Eid[Eid],

E.Did[Did],

D.Did[Did],

D.Dname[Dname]

FROM Employee E

FULL OUTER JOIN

Department D

ON E.Did=D.DeptId

Eid Did Dname
1 100 HR
2 200 TIS
3 500 NULL
NULL 50 DEV
Please log in to add an answer.