상호베타적(Exclusive-OR) 관계

윤상훈·2022년 2월 19일
0

SQLP

목록 보기
3/9

- 정의

아래와 같이 "주문"테이블이 있다고 할 때,
주문 테이블의 개인 / 법인번호 컬럼에는
부모 테이블 "개인고객" 또는 "법인고객"으로 부터
상속받은 값 중 하나가 들어가게 된다.

이러한 관계를 상호베타적 관계라고 한다.

- Table 생성

-- 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'이 출력되어야 할것으로 보이는데,
데이터 세팅이나 쿼리에 문제가 있는지 다시 한번 살펴보고 이후 수정하도록 하겠다.

profile
데이터를 사랑하는 개발자

0개의 댓글