SQL Tutorial : DRL / DQL - OUTER JOIN, CROSS JOIN

SQL Tutorial : DRL / DQL - OUTER JOIN

Let's learn about next topic: OUTER JOIN

OUTER JOIN:

*************

- INTERNALLY A EQUI JOIN WHICH DISPLAYS BOTH

- MATCHED AND UNMATCHED DATA WITH NULL VALUE COMPARISONS

EMP     DEPT

EID  NAME    DID DID  DNAME

100 A 10   10 SALES

101 B 20   20 SERVICE

102 C -   30 IT

103 D -   40 HR

104 E 20    

- SELECT E.LAST_NAME,E.SALARY ,D.DEPARTMENT_NAME

FROM EMPLOYEES E,DEPARTMENTS D

WHERE E.DEPARTMENT_ID =D.DEPARTMENT_ID(+)

- SELECT E.LAST_NAME,E.SALARY ,D.DEPARTMENT_NAME

FROM EMPLOYEES E,DEPARTMENTS D

WHERE E.DEPARTMENT_ID(+) =D.DEPARTMENT_ID

ANSI SQL:

- CROSS JOIN  SAME AS CARTESIAN PRODUCT

- SELECT LAST_NAME,SALARY,DEPARTMENT_NAME

FROM EMPLOYEES CROSS JOIN DEPARTMENTS

- NATURAL JOIN :  

T1  T2

DID A  B C D  DEPT_ID A B H J

Point to note:
- EQUI JOIN PERFORMED ON COLUMNS WITH SAME NAME

- SELECT LAST_NAME,SALARY,DEPARTMENT_NAME

FROM EMPLOYEES NATURAL  JOIN DEPARTMENTS
WHERE DEPARTMENT_ID IN(20,40)

USING CLAUSE:

***************

- SELECT E.LAST_NAME,E.SALARY,D.DEPARTMENT_NAME,

DEPARTMENT_ID

FROM EMPLOYEES  E JOIN DEPARTMENTS D

USING(DEPARTMENT_ID)

WHERE DEPARTMENT_ID IN(20,40)

- SELECT E.LAST_NAME,E.SALARY,D.DEPARTMENT_NAME,

DEPARTMENT_ID,L.CITY

FROM EMPLOYEES  E JOIN DEPARTMENTS D
USING(DEPARTMENT_ID)
JOIN LOCATIONS L
USING (LOCATION_ID)
WHERE DEPARTMENT_ID IN(20,40)

ON CLAUSE:

************

- SELECT E.LAST_NAME,E.SALARY,D.DEPARTMENT_NAME,

E.DEPARTMENT_ID

FROM EMPLOYEES  E JOIN DEPARTMENTS D
ON(E.DEPARTMENT_ID=D.DEPARTMENT_ID)
WHERE E.DEPARTMENT_ID IN(30,40)

LEFT OUTER JOIN:

******************

- SELECT E.LAST_NAME,E.SALARY,D.DEPARTMENT_NAME,

DEPARTMENT_ID
FROM EMPLOYEES  E LEFT OUTER JOIN DEPARTMENTS D
ON(E.DEPARTMENT_ID=D.DEPARTMENT_ID)

RIGHT OUTER JOIN:

********************

- SELECT E.LAST_NAME,E.SALARY,D.DEPARTMENT_NAME,

DEPARTMENT_ID

FROM EMPLOYEES  E RIGHT OUTER JOIN DEPARTMENTS D

ON(E.DEPARTMENT_ID=D.DEPARTMENT_ID)

FULL OUTER JOIN:

*******************

SELECT E.LAST_NAME,E.SALARY,D.DEPARTMENT_NAME,

DEPARTMENT_ID

FROM EMPLOYEES  E FULL OUTER JOIN DEPARTMENTS D

ON(E.DEPARTMENT_ID=D.DEPARTMENT_ID)