Different Types of JOINS in SQL with Example

Different Types of JOINS in SQL with Example

--====================================================

-- JOINS in SQL

--====================================================

Table 1:-  XX_EMP_TL  A

select * from XX_EMP_TL A

EMPNO	ENAME	MGR	SAL	DEPTNO
7369	Sunil	7934	800	20
7499	Anil		1600	60
7788	Sushil	7934	3000	20
7934	Vinod	7369	1300	10

Table 2:-  XX_DEPT_TL B

select * from XX_DEPT_TL B

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON
50	FINANCE	INDIA

select * from XX_EMP_TL A, XX_DEPT_TL B

• (INNER) JOIN: Returns records that have matching values in both tables

--====================================================

-- 1. Inner Joins

--====================================================

select * from XX_EMP_TL A, XX_DEPT_TL B

where A.DEPTNO = B.DEPTNO  

select * from XX_EMP_TL

select * from XX_DEPT_TL

• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

-====================================================

-- 2. Left OUTER Joins

--====================================================

select * from XX_EMP_TL A, XX_DEPT_TL B

where A.DEPTNO = B.DEPTNO (+)

SELECT * FROM XX_EMP_TL A

LEFT OUTER JOIN XX_DEPT_TL B

ON A.DEPTNO= B.DEPTNO

• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

--====================================================

-- 3. Right OUTER Joins

--====================================================

select * from XX_EMP_TL A, XX_DEPT_TL B

where A.DEPTNO(+) = B.DEPTNO

SELECT * FROM XX_EMP_TL A

RIGHT OUTER JOIN XX_DEPT_TL B

ON A.DEPTNO= B.DEPTNO

• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

--====================================================

-- 4. Full OUTER Joins

--====================================================

select * from XX_EMP_TL A, XX_DEPT_TL B

where A.DEPTNO(+) = B.DEPTNO

UNION

select * from XX_EMP_TL A, XX_DEPT_TL B

where A.DEPTNO = B.DEPTNO(+)

SELECT * FROM XX_EMP_TL A

FULL OUTER JOIN XX_DEPT_TL B

ON A.DEPTNO= B.DEPTNO

• Self JOIN: where we join 2 same tables for a different type of data.

--====================================================

-- 5. Self Joins

--====================================================

select A.EMPNO,A.ENAME, A.MGR, B.ENAME MGR_NAME  from XX_EMP_TL A, XX_EMP_TL B

where A.MGR =  B.EMPNO