CREATE TABLE ACCOUNT_INFO
(ACC_TYPE VARCHAR2(1 CHAR) NOT NULL
,ACC_NAME VARCHAR2(50 CHAR) NOT NULL
,OWNER_ID VARCHAR2(10 CHAR) NOT NULL
,CONSTRAINT "ACCOUNT_INFO_PK" PRIMARY KEY ("ACC_TYPE", "OWNER_ID"));

CREATE TABLE USER_INFO
(USER_ID VARCHAR2(10 CHAR) NOT NULL
,USER_NAME VARCHAR2(10 CHAR) NOT NULL
,CONSTRAINT "USER_INFO_PK" PRIMARY KEY ("USER_ID"));

CREATE TABLE COMP_INFO
(COMP_ID VARCHAR2(10 CHAR) NOT NULL
,COMP_NAME VARCHAR2(10 CHAR) NOT NULL
,CONSTRAINT "COMP_INFO_PK" PRIMARY KEY ("COMP_ID"));

INSERT INTO ACCOUNT_INFO VALUES ('U', '사용자1 계좌', 'USER01');
INSERT INTO ACCOUNT_INFO VALUES ('U', '사용자2 계좌', 'USER02');
INSERT INTO ACCOUNT_INFO VALUES ('C', '기업1 계좌', 'COMP01');
INSERT INTO ACCOUNT_INFO VALUES ('C', '기업2 계좌', 'COMP02');
INSERT INTO ACCOUNT_INFO VALUES ('C', '기업3 계좌', 'COMP03');

INSERT INTO USER_INFO VALUES ('USER01', '사용자1 이름');
INSERT INTO USER_INFO VALUES ('USER02', '사용자2 이름');

INSERT INTO COMP_INFO VALUES ('COMP01', '기업1 이름');
INSERT INTO COMP_INFO VALUES ('COMP02', '기업2 이름');
INSERT INTO COMP_INFO VALUES ('COMP03', '기업3 이름');
COMMIT;


-- 1. ORACLE Query
--------------------------------------------------------------------------------
SELECT  A.*
,       NVL(U.USER_NAME, C.COMP_NAME) AS OWNER_NAME
FROM ACCOUNT_INFO A, USER_INFO U, COMP_INFO C
WHERE U.USER_ID(+) = DECODE(A.ACC_TYPE, 'U', A.OWNER_ID)
AND   C.COMP_ID(+) = DECODE(A.ACC_TYPE, 'C', A.OWNER_ID);


SELECT  A.*
,       NVL(U.USER_NAME, C.COMP_NAME) AS OWNER_NAME
FROM ACCOUNT_INFO A, USER_INFO U, COMP_INFO C
WHERE U.USER_ID(+) = A.OWNER_ID
AND   C.COMP_ID(+) = A.OWNER_ID;



-- 2. ANSI Query
--------------------------------------------------------------------------------
SELECT  A.*
,       NVL(U.USER_NAME, C.COMP_NAME) AS OWNER_NAME
FROM ACCOUNT_INFO A
LEFT OUTER JOIN USER_INFO U ON  U.USER_ID = A.OWNER_ID
                            AND 'U'       = A.ACC_TYPE

LEFT OUTER JOIN COMP_INFO C ON  C.COMP_ID = A.OWNER_ID
                            AND 'C'       = A.ACC_TYPE;


SELECT  A.*
,       NVL(U.USER_NAME, C.COMP_NAME) AS OWNER_NAME
FROM ACCOUNT_INFO A
LEFT OUTER JOIN USER_INFO U ON  U.USER_ID = A.OWNER_ID
LEFT OUTER JOIN COMP_INFO C ON  C.COMP_ID = A.OWNER_ID;




ACC_TYPE ACC_NAME                                           OWNER_ID   OWNER_NAME
-------- -------------------------------------------------- ---------- ----------
U        사용자1 계좌                                        USER01     사용자1 이름 
U        사용자2 계좌                                        USER02     사용자2 이름 
C        기업1 계좌                                          COMP01     기업1 이름  
C        기업2 계좌                                          COMP02     기업2 이름  
C        기업3 계좌                                          COMP03     기업3 이름