JOIN IN SQL

JOINS- Joins are use to join two or more than two table. Joins are use to overcome disadvantage of subquery.

Types of joins

1. Cross Joins
2. Inner Joins
3. Natural Joins
4. Outer Joins (Left,Right,Full Outer Join)
5. Joins(Self Join)

1.Cross Joins-It is use to obtain match record and plenty of unmatched record.

ORACLE SYNTAX-

SELECT *

FROM TABLE1 , TABLE2

WHERE <FILTER_CONDITION>;

ANSI(AMERICAN NATIONAL STANDARDS INSITIUE) 

SELECT *
FORM TABLE1 CROSS JOIN TABLE2
WHERE <FILTER_CONDITION>;

 

Number of match record are very less than number of unmatched record.
This is only disadvantage of cross join. So we will not use cross join but the remaining type of join are derived from cross join. 

2.Inner Join- It is use to obtain only match record with the help of join condition.

CC-(COMMON COLUMNS)

ORACLE SYNTAX

SELECT *

FROM TABLE 1 , TABLE 2

WHERE <FILTER_CONDITION> AND TABLE 1.CC = TABLE 2.CC;

ANSI -

SELECT *

FROM TABLE 1 INNER JOIN TABLE 2

ON TABLE 1 . CC =TABLE 2. CC

WHERE <FILTER_CONDITION>;

3.Natural Join - Natural join is also use to obtain the match record without using join condition.
The compiler identified the common columns and take it only once in output table.

Note-Oracle does not support natural join So there is no oracle syntax.

ANSI -

SELECT *

FROM TABLE 1 NATURAL JOIN TABLE 2

WHERE <FILTER_CONDITION>;

 

4.Outer Join - It is used to obtain the match records of both the table and  unmatched record of required table.

There are  three type-

1. Left Outer Join.
2. Right Outer Join.
3. Full Outer Join.

1) Left Outer Join - It is used to obtain the match record of both the table and unmatched record of left table.

ORACLE SYNTAX-

SELECT *

FROM TABLE 1. TABLE 2

WHERE TABLE 1.CC = TABLE 2.CC AND <FILTER_CONDITION>;

ANSI-

SELECT *

FROM TABLE 1  LEFT OUTER JOIN TABLE 2

ON TABLE 1. CC =TABLE 2. CC

WHERE <FILTER_CONDITION>;

2) Right Outer Join - It is use to obtain the match record of both the table and unmatched record of right table.

ORACLE SYNTAX-

SELECT *

FORM TABLE 1 , TABLE 2

WHERE TABLE 1 . CC =TABLE 2. CC(+) AND <FILTER_CONDITION>;

ANSI -

SELECT *

FROM TABLE 1 . CC RIGHT OUTER JOIN TABLE 2

ON TABLE 1.CC = TABLE 2 .CC

WHERE <FILTER_CONDITION>; 

3)Full Outer Join - Full outer join is use to obtain the match record of both the table and unmatched record of the both the table. 

No oracle syntax

ANSI -

SELECT *

FROM TABLE  1 FULL OUTER JOIN TBALE 2

ON TABLE 1.CC=TABLE 2.CC

WHERE <FILTER_CONDITION>; 

5. Join(self join)- Self Join is used to join the table with it self.

ORACLE SYNTAX-

SELECT T1.* , T2.*

FROM TABLE 1 T1 , TBALE 1 T2

WHERE <JOIN_CONDITION> AND <FILTER_CONDITION>;

ANSI- 

SELECT T1.* , T2.*

FROM TABLE 1.T1 JOIN TBALE 1 T2

ON <JOIN_CONDITION>

WHERE <FILTER_CONDITION>;

NOTE- Aalias name and qualifier are mandatory for every column.