[DB] WITH 문 (가상 테이블)

준비된 테이블, 데이터 (TMP_A, TMP_B)

SELECT * FROM TMP_A;

	M
1	A
2	C

 

SELECT * FROM TMP_B;

	M
1	B
2	D
3	A

 

---

WITH 문으로 쿼리를 묶어 

가상테이블을 만든 후

간단히 사용 가능

 

예제  1)

WITH TABLE_INNER AS 
(
SELECT * 
FROM TMP_A A
INNER JOIN TMP_B B
ON (A.M = B.M)
)

SELECT * FROM TABLE_INNER
;

	M	M_1
1	A	A

 

예제  2)

WITH TABLE_INNER AS 
(
SELECT * 
FROM TMP_A A
LEFT OUTER JOIN TMP_B B
ON (A.M = B.M)
)

SELECT * FROM TABLE_INNER
;

	M	M_1
1	A	A
2	C	(null)

 

예제  3)

WITH TABLE_INNER AS 
(
SELECT * 
FROM TMP_A A
RIGHT OUTER JOIN TMP_B B
ON (A.M = B.M)
)

SELECT * FROM TABLE_INNER
;

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

 

예제 4)

WITH TABLE_INNER AS 
(
SELECT A.M AS "AM"
     , B.M AS "BM"
FROM TMP_A A
RIGHT OUTER JOIN TMP_B B
ON (A.M = B.M)
)

SELECT * 
FROM TABLE_INNER
WHERE AM IS NULL
;

	AM	BM
1	(null)	D
2	(null)	B

 

예제 5)

WITH TABLE_INNER AS 
(
SELECT A.M AS "AM"
     , B.M AS "BM"
FROM TMP_A A
RIGHT OUTER JOIN TMP_B B
ON (A.M = B.M)
)

SELECT * 
FROM TABLE_INNER
WHERE AM IS NOT NULL
;

	AM	BM
1	A	A

 

 

---

추가 예제)

WITH TABLE_A AS
(
    SELECT 'A' AS "S" FROM DUAL
    UNION ALL
    SELECT 'B' AS "S" FROM DUAL
)

SELECT * FROM TABLE_A ;

 

 

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

SELECT * FROM TABLE_A
UNION ALL 
SELECT * FROM TABLE_B ;

 

 

WITH 
TABLE_A AS
(
    SELECT 'A' AS "S" FROM DUAL
    UNION ALL
    SELECT 'B' AS "S" FROM DUAL
),
TABLE_B AS
(
    SELECT 'C' AS "S" FROM DUAL
    UNION ALL
    SELECT 'D' AS "S" FROM DUAL
),
TABLE_C AS
(
    SELECT 'E' AS "S" FROM DUAL
    UNION ALL
    SELECT 'F' AS "S" FROM DUAL
)

SELECT * FROM TABLE_A
UNION ALL 
SELECT * FROM TABLE_B 
UNION ALL 
SELECT * FROM TABLE_C ;