written 6.2 years ago by |
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 |