rgr.kr sql 질문

testSql.sql

WITH T1 AS (
SELECT 1 AS A , 'X' AS B , 'X' AS C FROM DUAL UNION ALL
SELECT 2  , 'X' , 'X' C FROM DUAL UNION ALL
SELECT 3  , 'X', 'X' C FROM DUAL
), T2 AS (
SELECT 1 AS A1, NULL AS A2, 'Y' AS B , 'Y' AS C FROM DUAL UNION ALL
SELECT NULL,2   ,'Y','Y' C FROM DUAL UNION ALL
SELECT 3,NULL  ,'Y','Y' C FROM DUAL
)
SELECT T1.* ,T3.*
FROM T1
JOIN (
SELECT CASE WHEN A1 IS NULL THEN A2 
        WHEN A2 IS NULL THEN A1 END AS A 
        , B
        , C 
        FROM T2
) T3 ON (T1.A = T3.A)
;

--         A B C          A B C
------------ - - ---------- - -
--         1 X X          1 Y Y
--         2 X X          2 Y Y
--         3 X X          3 Y Y

WITH T1 AS (
SELECT 1 AS A , 'X' AS B , 'X' AS C FROM DUAL UNION ALL
SELECT 2  , 'X' , 'X' C FROM DUAL UNION ALL
SELECT 3  , 'X', 'X' C FROM DUAL
), T2 AS (
SELECT 1 AS A1, 2 AS A2, 'Y' AS B , 'Y' AS C FROM DUAL UNION ALL
SELECT 2,3   ,'Y','Y' C FROM DUAL UNION ALL
SELECT 3,2  ,'Y','Y' C FROM DUAL
)
--SELECT * FROM T1;
--SELECT * FROM T2;
SELECT T1.* ,T2.*
FROM T1
LEFT JOIN T2 ON T1.A = T2.A1 OR T1.A = T2.A2
;

--         A B C         A1         A2 B C
------------ - - ---------- ---------- - -
--         1 X X          1          2 Y Y
--         2 X X          1          2 Y Y
--         2 X X          2          3 Y Y
--         2 X X          3          2 Y Y
--         3 X X          2          3 Y Y
--         3 X X          3          2 Y Y

-- 스티플
WITH T1 AS (
SELECT 1 AS A , 'X' AS B , 'X' AS C FROM DUAL UNION ALL
SELECT 2  , 'X' , 'X' C FROM DUAL UNION ALL
SELECT 3  , 'X', 'X' C FROM DUAL
), T2 AS (
SELECT 1 AS A1, 2 AS A2, 'Y' AS B , 'Y' AS C FROM DUAL UNION ALL
SELECT 2,3   ,'Y','Y' C FROM DUAL UNION ALL
SELECT 3,2  ,'Y','Y' C FROM DUAL
)
--SELECT * FROM T1;
--SELECT * FROM T2;
SELECT T1.* ,T2.*, T3.*
FROM T1
LEFT JOIN T2 ON T1.A = T2.A1 
LEFT JOIN T2 T3 ON  T1.A = T3.A2
ORDER BY T1.A
;

--         A B C         A1         A2 B C         A1         A2 B C
------------ - - ---------- ---------- - - ---------- ---------- - -
--         1 X X          1          2 Y Y                          
--         2 X X          2          3 Y Y          1          2 Y Y
--         2 X X          2          3 Y Y          3          2 Y Y
--         3 X X          3          2 Y Y          2          3 Y Y