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

Department Table

SELECT E.Eid[Eid],

D.Did[Did]

D.Dname[Dname]

FROM Employee E

NATURAL JOIN

Department D

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

Department Table

SELECT e.Eid[Eid],

e.Ename[Ename]

e.Did[Did]

d.Did[Did]

d.Dname[Dname]

FROM Employee e

CROSS JOIN

Department d

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)

Manager Table(M)

SELECT E.Ename[Employee],

M.Ename[Manager]

FROM Employee E

CROSS

EmployeeM

ON E.Mid=M.Eid

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

Department Table

SELECT E.Eid[Eid],

D.Did[Did]

D.Dname[Dname]

FROM Employee E

INNER JOIN

Department D

ON E.Did=D.DeptId

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

Department Table

SELECT E.Eid[Eid],

D.Did[Did]

D.Dname[Dname]

FROM Employee E

LEFT OUTER JOIN

Department D

ON E.Did=D.DeptId

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

Department Table

SELECT E.Eid[Eid],

D.Did[Did]

D.Dname[Dname]

FROM Employee E

RIGHT OUTER JOIN

Department D

ON E.Did=D.DeptId

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

Department Table

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