[DB] ORACLE JOIN, ANSI JOIN

---

가상 테이블 TABLE_A, TABLE_B

WITH 
TABLE_A AS
(
    SELECT 'A' AS "M" FROM DUAL
    UNION ALL
    SELECT 'B' AS "M" FROM DUAL
    UNION ALL
    SELECT 'C' AS "M" FROM DUAL
),
 TABLE_B AS
(
    SELECT 'A' AS "M" FROM DUAL
    UNION ALL
    SELECT 'B' AS "M" FROM DUAL
    UNION ALL
    SELECT 'D' AS "M" FROM DUAL
)

 

TABLE_A

	M
1	A
2	B
3	C

 

TABLE_B

	M
1	A
2	B
3	D

---

INNER JOIN

 

[ORACLE] INNER JOIN

-- [ORACLE] INNER JOIN
SELECT * 
FROM TABLE_A A, TABLE_B B
WHERE A.M = B.M ;

 

[ANSI] INNER JOIN

-- [ANSI] INNER JOIN
SELECT *
FROM TABLE_A A
INNER JOIN TABLE_B B
ON (A.M = B.M) ;

	M	M_1
1	A	A
2	B	B

---

LEFT OUTER JOIN

 

[ORACLE] LEFT OUTER JOIN

-- [ORACLE] LEFT OUTER JOIN
SELECT *
FROM TABLE_A A, TABLE_B B
WHERE A.M = B.M(+) ;

 

[ANSI] LEFT OUTER JOIN

-- [ANSI] LEFT OUTER JOIN 
SELECT * 
FROM TABLE_A A
LEFT OUTER JOIN TABLE_B B
ON (A.M = B.M) ;

	M	M_1
1	A	A
2	B	B
3	C	(null)

---

RIGHT OUTER JOIN

 

[ORACLE] RIGHT OUTER JOIN

-- [ORACLE] RIGHT OUTER JOIN
SELECT *
FROM TABLE_A A, TABLE_B B
WHERE A.M(+) = B.M ;

 

[ANSI] RIGHT OUTER JOIN

-- [ANSI] RIGHT OUTER JOIN
SELECT *
FROM TABLE_A A
RIGHT OUTER JOIN TABLE_B B
ON (A.M = B.M) ;

M	M_1
1	A	A
2	B	B
3	(null)	D

---

CROSS JOIN

 

[ORACLE] CROSS JOIN

-- [ORACLE] CROSS JOIN
SELECT *
FROM TABLE_A A, TABLE_B B ;

 

[ANSI] CROSS JOIN

-- [ANSI] CROSS JOIN
SELECT *
FROM TABLE_A A
CROSS JOIN TABLE_B B ;

	M	M_1
1	A	A
2	A	B
3	A	D
4	B	A
5	B	B
6	B	D
7	C	A
8	C	B
9	C	D

---