아래와 같이 "주문"테이블이 있다고 할 때,
주문 테이블의 개인 / 법인번호 컬럼에는
부모 테이블 "개인고객" 또는 "법인고객"으로 부터
상속받은 값 중 하나가 들어가게 된다.
이러한 관계를 상호베타적 관계라고 한다.
-- Table 개인고객
CREATE TABLE "개인고객"
("개인번호" VARCHAR2(10 BYTE), "개인고객명" VARCHAR2(20 BYTE));
-- Index 개인고객_PK
CREATE UNIQUE INDEX "개인고객_PK" ON "개인고객" ("개인번호");
-- Constraints for Table 개인고객
ALTER TABLE "개인고객" ADD CONSTRAINT "개인고객_PK" PRIMARY KEY ("개인번호");
-- Table 법인고객
CREATE TABLE "SQLPADMIN"."법인고객"
("법인번호" VARCHAR2(10 BYTE), "법인명" VARCHAR2(20 BYTE));
-- Index 법인고객_PK
CREATE UNIQUE INDEX "법인고객_PK" ON "법인고객" ("법인번호");
-- Constraints for Table 법인고객
ALTER TABLE "법인고객" ADD CONSTRAINT "법인고객_PK" PRIMARY KEY ("법인번호");
-- Table 주문
CREATE TABLE "주문"
("주문번호" VARCHAR2(7 BYTE), "고객구분코드" VARCHAR2(20 BYTE),
"개인법인번호" VARCHAR2(10 BYTE));
-- Index 주문_PK
CREATE UNIQUE INDEX "주문_PK" ON "주문" ("주문번호");
-- Constraints for Table 주문
ALTER TABLE "주문" ADD CONSTRAINT "주문_PK" PRIMARY KEY ("주문번호");
INSERT INTO "개인고객" ("개인번호", "개인고객명") VALUES ('1234', '홍길동');
INSERT INTO "개인고객" ("개인번호", "개인고객명") VALUES ('1356', '곽두팔');
INSERT INTO "개인고객" ("개인번호", "개인고객명") VALUES ('2556', '최민림');
INSERT INTO "법인고객" ("법인번호", "법인명") VALUES ('1122334455', '주)카밀');
INSERT INTO "법인고객" ("법인번호", "법인명") VALUES ('2233445566', '주)해달');
INSERT INTO "주문" ("주문번호", "고객구분코드", "개인법인번호") VALUES ('1100001', '01', '1234');
INSERT INTO "주문" ("주문번호", "고객구분코드", "개인법인번호") VALUES ('1100002', '02', '1122334455');
INSERT INTO "주문" ("주문번호", "고객구분코드", "개인법인번호") VALUES ('1100003', '01', '1356');
INSERT INTO "주문" ("주문번호", "고객구분코드", "개인법인번호") VALUES ('1100004', '01', '2556');
INSERT INTO "주문" ("주문번호", "고객구분코드", "개인법인번호") VALUES ('1100005', '02', '2233445566');
이제 데이터가 준비 되었다면 UNION ALL 과 COALESCE를 통해 개인고객명을 조회해보자.
-- UNION ALL
SELECT B.개인고객명
FROM 주문 A, 개인고객 B
WHERE A.주문번호 = 1100001
AND A.고객구분코드 = '01' -- 고객구분코드 '01': 개인고객
AND A.개인법인번호 = B.개인번호
UNION ALL
SELECT B.법인명
FROM 주문 A, 법인고객 B
WHERE A.주문번호 = 1100001
AND A.고객구분코드 = '02' -- 고객구분코드 '02': 법인고객
AND A.개인법인번호 = B.법인번호;
-- COALESCE
-- 개인번호와 법인번호가 중복되지 않는다는 전제하에
SELECT COALESCE(B.개인고객명, C.법인명) 고객명
FROM 주문 A
LEFT OUTER JOIN 개인고객 B
ON (A.개인법인번호 = B.개인번호)
LEFT OUTER JOIN 법인고객 C
ON (A.개인법인번호 = C.법인번호)
WHERE A.주문번호 = 1100001;
위에서 보듯이, 있는 데이터를 조회한다면 둘 다 1건의 데이터를 잘 조회하고 있음을 알 수 있다.
반대로, 없는 데이터를 조회한다면 어떤 결과가 나올까?
-- UNION ALL
SELECT B.개인고객명
FROM 주문 A, 개인고객 B
WHERE A.주문번호 = 1100009 -- '1100001' --> '1100009'
AND A.고객구분코드 = '01'
AND A.개인법인번호 = B.개인번호
UNION ALL
SELECT B.법인명
FROM 주문 A, 법인고객 B
WHERE A.주문번호 = 1100009 -- '1100001' --> '1100009'
AND A.고객구분코드 = '02'
AND A.개인법인번호 = B.법인번호;
-- COALESCE
-- 개인번호와 법인번호가 중복되지 않는다는 전제하에
SELECT COALESCE(B.개인고객명, C.법인명) 고객명
FROM 주문 A
LEFT OUTER JOIN 개인고객 B
ON (A.개인법인번호 = B.개인번호)
LEFT OUTER JOIN 법인고객 C
ON (A.개인법인번호 = C.법인번호)
WHERE A.주문번호 = 1100009; -- '1100001' --> '1100009'
두 쿼리 모두 동일한 값을 리턴하는 듯 보이지만
<SQL 전문가 가이드, 한국데이터산업진흥원> p.103에 따르면,
UNION ALL은 JOIN 결과가 없는 경우 공집합 NO ROWS를 출력하는 반면
COALESCE + LEFT OUTER JOIN은 NULL ROWS라는 1건을 출력한다고 한다.
실제로 조회된 결과가 '공집합'인지 'Null'인지 NVL 함수를 사용하여 확인해보자
SELECT NVL(U.고객명, 'NULL') AS 결과
FROM(
SELECT B.개인고객명 AS 고객명
FROM 주문 A, 개인고객 B
WHERE A.주문번호 = 1100009
AND A.고객구분코드 = '01'
AND A.개인법인번호 = B.개인번호
UNION ALL
SELECT B.법인명 AS 고객명
FROM 주문 A, 법인고객 B
WHERE A.주문번호 = 1100009
AND A.고객구분코드 = '02'
AND A.개인법인번호 = B.법인번호
) U;
공집합은 NULL과 다르기 때문에 NVL함수에 걸리지 않아 공집합이 그대로 출력됨을 알 수 있다.
SELECT NVL(COALESCE(B.개인고객명, C.법인명),'NULL') 고객명
FROM 주문 A
LEFT OUTER JOIN 개인고객 B
ON (A.개인법인번호 = B.개인번호)
LEFT OUTER JOIN 법인고객 C
ON (A.개인법인번호 = C.법인번호)
WHERE A.주문번호 = 1100009;
실제 실습결과 마지막 쿼리에서 'NULL'이 출력되어야 할것으로 보이는데,
데이터 세팅이나 쿼리에 문제가 있는지 다시 한번 살펴보고 이후 수정하도록 하겠다.