타이의 노트

[데이터베이스시스템및응용] 4. 외부 조인과 NULL

1. NULL과 3값 논리

NULL이 포함된 일반적인 비교 연산의 결과는 UNKNOWN이라 표시한다

3값 논리

SQL의 조건식은 TRUE, FALSE, UNKNOWN의 세 가지 결과를 가질 수 있다. WHERE는 조건식의 결과가 TRUE인 행만 남긴다. FALSE와 UNKNOW인 행은 모두 제외한다

NULL이 포함된 조건들을 AND, OR, NOT으로 결합하면 다음과 같이 평가한다

ABA AND BA OR B
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
TRUEUNKNOWNUNKNOWNTRUE
FALSETRUEFALSETRUE
FALSEFALSEFALSEFALSE
FALSEUNKNOWNFALSEUNKNOWN
UNKNOWNTRUEUNKNOWNTRUE
UNKNOWNFALSEFALSEUNKNOWN
UNKNOWNUNKNOWNUNKNOWNUNKNOWN
ANOT A
TRUEFALSE
FALSETRUE
UNKNOWNUNKNOWN

이를 기억하기 위해 FALSE를 0, UNKNOWN을 0.5, TRUE를 1로 대응시킬 수 있다. 이 대응에서는 다음과 같이 표현된다

  • AND = min(A, B)
  • OR = max(A, B)
  • NOT A = 1 - A

논리를 설명하기 위한 대응이며, SQL의 실제 숫자 값이라는 뜻은 아니다

1.1 NULL이 포함된 WHERE 조건

다음과 같은 Toys 테이블이 있다고 하자

NamePriceSizeColor
iPad Pro109912gray
BicycleNULLNULLred
Freeze Tag0NULLNULL
iPad Air59910NULL
SELECT *
FROM Toys
WHERE price < 1000
  AND (size = 2 OR color = 'red');

각 행의 조건식은 다음과 같이 평가된다

Nameprice < 1000size = 2 OR color = ‘red’전체 조건
iPad ProFALSEFALSEFALSE
BicycleUNKNOWNTRUEUNKNOWN
Freeze TagTRUEUNKNOWNUNKNOWN
iPad AirTRUEUNKNOWNUNKNOWN

따라서 조회 결과는 0행이다. Bicycle은 빨간색이지만 가격 조건이 UNKNOWN이므로 선택되지 않는다

1.2 NULL 검사: IS NULL과 IS NOT NULL

NULL 여부는 IS NULL또는 IS NOT NULL로 검사한다. = NULL이나 <> NULL은 NULL 여부를 확인하는 조건으로 사용할 수 없다

-- 가격을 모르는 상품
SELECT *
FROM Toys
WHERE price IS NULL;
 
-- 가격이 있는 상품
SELECT *
FROM Toys
WHERE price IS NOT NULL;

첫 번째 쿼리는 Bicycle을 반환하고, 두 번째 쿼리는 나머지 세 상품을 반환한다

1.3 항상 참처럼 보이는 조건의 주의점

SELECT *
FROM Toys
WHERE price <= 100 OR price > 100;

가격이 있는 상품은 두 조건 중 하나를 만족한다. 하지만 Bicycle은 두 비교가 모두 UNKNOWN이므로 전체 조건도 UNKNOWN이다. 따라서 Bicycle을 제외한 3행만 반환한다

3값 논리에서는 A OR NOT A가 항상 TRUE인 것은 아니다. A가 UNKNOWN이면 UNKNOWN OR UNKNOWN이 되어 결과도 UNKNOWN이다

가격이 NULL인 상품까지 포함하려면 NULL 조건을 명시해야 한다

SELECT *
FROM Toys
WHERE price <= 100
   OR price > 100
   OR price IS NULL;

이 쿼리는 네 상품을 모두 반환한다

1.4 NOT NULL 제약과 쿼리 최적화

NULL을 허용하는 열에서는 조건식이 UNKNOWN이 될 수 있으므로, DMBS는 일반적인 2값 논리만으로 조건을 단순화해서는 안 된다

예를 들어 앞의 price <= 100 OR price > 100을 무조건 참이라고 보고 제거하면, 원래 제외되던 NULL 행까지 반환하여 결과가 달라진다

새 테이블을 다음과 같이 정의하면 Price에는 NULL을 저장할 수 없다

CREATE TABLE Toys (
    Name  VARCHAR(256) NOT NULL,
    Price INT NOT NULL,
    Size  INT,
    Color VARCHAR(256)
);

이 정의에서는 모든 가격이 정수 값이므로 price <= 100 OR price > 100은 항상 TRUE이다. 따라서 DBMS가 이 제약을 활용하면 해당 조건을 제거하는 최적화가 가능하다. NOT NULL은 데이터의 유효성을 보장할 뿐 아니라, DBMS가 쿼리를 안전하게 단순화할 수 있는 근거가 된다





2. 외부 조인

내부 조인은 조인 조건을 만족하는 행의 조합만 반환한다. 따라서 다른 테이블에 대응하는 행이 없으면 결과에서 빠진다

다음은 직원 정보인 Payroll과 차량 등록 정보인 Registry이다

Payroll

UserIDNameJobSalary
123LeslieTA50000
345FrancesTA60000
567MagdaProf120000
789QuinnProf100000

Registry

UserIDCar
123Charger
567Civic
567Ferrari
007Aston Martin

직원과 차량을 내부 조인하면 다음과 같다

SELECT P.Name, R.Car
FROM Payroll AS P
INNER JOIN Registry AS R
    ON P.UserID = R.UserID;
NameCar
LeslieCharger
MagdaCivic
MagdaFerrari

Frances와 Quinn은 대응하는 차량 등록 행이 없으므로 결과에 나타나지 않는다. 차량 등록 여부와 관계없이 모든 직원을 조회하려면 외부 조인을 사용한다

2.1 외부 조인의 정의와 종류

외부 조인(Outer Join)

조인 조건을 만족하는 행의 조합을 반환하면서, 지정된 쪽에서 대응하는 행이 없는 행도 결과에 포함한다. 대응하는 행이 없는 쪽의 열은 NULL로 채운다

종류

종류결과에 보존하는 행
LEFT OUTER JOIN왼쪽 테이블의 모든 행
RIGHT OUTER JOIN오른쪽 테이블의 모든 행
FULL OUTER JOIN양쪽 테이블의 모든 행

OUTER는 생략할 수 있다. 예를 들어 LEFT JOIN과 LEFT OUTER JOIN은 같은 의미이다

행 보존과 결과 행 수

모든 행을 보존한다는 것은 각 행이 결과에 최소 한 번 포함된다는 뜻이다. 한 행에 여러 행이 대응하면 여러 조합을 반환하므로, 결과 행 수가 보존하는 테이블의 행 수보다 많아질 수 있다

2.2.1 LEFT OUTER JOIN

왼쪽 외부 조인(LEFT OUTER JOIN)

왼쪽 테이블의 모든 행을 보존한다. 오른쪽에 대응하는 행이 있으면 조합을 반환하고, 없으면 오른쪽 열을 NULL로 채운다

SELECT P.Name, R.Car
FROM Payroll AS P
LEFT OUTER JOIN Registry AS R
    ON P.UserID = R.UserID;
NameCar
LeslieCharger
FrancesNULL
MagdaCivic
MagdaFerrari
QuinnNULL
  • Frances와 Quinn도 결과에 포함되며, Car는 NULL이다
  • Magda는 차량 등록 행이 두 개이므로 두 행으로 나타난다
  • Registry의 UserID 007은 Payroll에 대응하는 행이 없으므로 포함되지 않는다

2.2.2 RIGHT OUTER JOIN

오른쪽 외부 조인(RIGHT OUTER JOIN)

오른쪽 테이블의 모든 행을 보존한다. 왼쪽에 대응하는 행이 없으면 왼쪽 열을 NULL로 채운다

SELECT P.Name, R.Car
FROM Payroll AS P
RIGHT OUTER JOIN Registry AS R
    ON P.UserID = R.UserID;
NameCar
LeslieCharger
MagdaCivic
MagdaFerrari
NULLAston Martin

Registry의 모든 행이 포함된다. UserID 007은 대응하는 직원이 없으므로 Name이 NULL이다. 반면 Frances와 Quinn은 대응하는 차량 등록 행이 없으므로 포함되지 않는다. 테이블의 위치를 바꾸면 같은 결과를 왼쪽 외부 조인으로 표현할 수 있다

2.2.3 FULL OUTER JOIN

완전 외부 조인(FULL OUTER JOIN)

양쪽 테이블의 모든 행을 보존한다. 조인 조건을 만족하는 행은 결합하고, 어느 쪽이든 대응하는 행이 없으면 반대쪽 열을 NULL로 채운다

SELECT P.Name, R.Car
FROM Payroll AS P
FULL OUTER JOIN Registry AS R
    ON P.UserID = R.UserID;
NameCar
LeslieCharger
FrancesNULL
MagdaCivic
MagdaFerrari
QuinnNULL
NULLAston Martin

차량 등록 행이 없는 직원과 직원 정보가 없는 차량 등록 행을 모두 포함한다

2.3 외부 조인의 활용과 주의점

왼쪽 외부 조인은 대응하는 데이터가 0개 이상일 수 있는 관계에서 자주 사용한다

조회 목적왼쪽 테이블오른쪽 테이블
제품이 없는 회사도 포함하여 조회회사제품
수강 내역이 없는 학생도 포함하여 조회학생수강 내역
주문이 없는 고객도 포함하여 조회고객주문
외부 조인은 대응하는 행이 없는 경우도 보존해야 하므로, 내부 조인보다 조인 순서 변경 등의 최적화에 제약이 생길 수 있다. 모든 행을 보존해야 하는지 확인하고 목적에 맞는 조인을 선택한다

특히 같은 조인 조건이라도 A LEFT JOIN B와 B LEFT JOIN A는 보존하는 테이블이 다르므로 일반적으로 결과가 다르다. 여러 외부 조인을 연결할 때도 결합 순서를 임의로 바꾸면 결과가 달라질 수 있다

2.3.1 ON과 WHERE의 차이

ON은 어떤 행을 서로 대응시킬지 결정하고, WHERE는 조인으로 만들어진 결과를 필터링한다. 왼쪽 외부 조인 뒤에 오른쪽 열을 비교하는 WHERE 조건을 추가하면, NULL로 채워진 행이 제외될 수 있다

SELECT P.Name, R.Car
FROM Payroll AS P
LEFT JOIN Registry AS R
    ON P.UserID = R.UserID
WHERE R.Car = 'Civic';

Frances와 Quinn의 R.Car는 NULL이므로 조건이 UNKNOWN이 된다. 따라서 이 쿼리는 Magda의 Civic 행만 반환한다

모든 직원을 포함하면서 Civic에 해당하는 차량 등록 정보만 연결하려면 조건을 ON에 둔다

SELECT P.Name, R.Car
FROM Payroll AS P
LEFT JOIN Registry AS R
    ON P.UserID = R.UserID
   AND R.Car = 'Civic';
NameCar
LeslieNULL
FrancesNULL
MagdaCivic
QuinnNULL
이 경우 Leslie도 조인 조건에 맞는 차량이 없으므로 Car가 NULL이다. 외부 조인이 만든 NULL은 원본 테이블의 값이 NULL이었다는 뜻은 아니다




3. 자기 조인

자기 조인(Self Join)

같은 테이블을 한 쿼리에서 여러 번 참조하여 조인하는 것이다. 각 참조에 서로 다른 별칭을 붙여, 같은 테이블의 행들을 서로 비교하거나 결합한다

FROM Registry AS R1
JOIN Registry AS R2
    ON R1.UserID = R2.UserID

R1과 R2는 같은 Registry 테이블을 가리키지만, 각 참조에서 선택되는 행은 다를 수 있다. 별칭을 붙인다고 실제 테이블이 복사되거나 새로 생성되는 것은 아니다. 자기 조인은 별도의 SELF JOIN 문법이 아니라, 같은 테이블을 대상으로 사용하는 조인 방식이다

3.1 같은 열에 AND를 적용하는 것과의 차이

앞의 예제와 달리, 여기서는 Leslie의 차량을 Civic으로 바꾸고 차량 등록 정보 중 다음 세 행을 사용한다

UserIDCar
123Civic
567Civic
567Ferrari

Civic과 Ferrari를 모두 등록한 직원을 찾으려 한다고 하자

SELECT P.Name, R.Car
FROM Payroll AS P
JOIN Registry AS R
    ON P.UserID = R.UserID
WHERE R.Car = 'Civic'
  AND R.Car = 'Ferrari';

이 쿼리는 0행을 반환한다. 조건은 직원의 모든 차량을 한꺼번에 검사하는 것이 아니라, 현재 조합에 있는 한 행의 R.Car 값을 검사하기 때문이다. R.Car = 'Civic' AND R.Car = 'Ferrari'는 하나의 값이 Civic이면서 동시에 Ferrari여야 한다는 뜻이다. 같은 사람이 가진 서로 다른 행을 검사하려면 각 행을 별도로 참조해야 한다

3.2 모든 행의 쌍 만들기

자기 조인을 이해하려면 같은 테이블에서 모든 행의 쌍을 만드는 것부터 생각할 수 있다

SELECT R1.UserID, R1.Car, R2.UserID, R2.Car
FROM Registry AS R1
CROSS JOIN Registry AS R2;

이는 다음과 같은 쉼표 방식과 같은 의미이다

SELECT R1.UserID, R1.Car, R2.UserID, R2.Car
FROM Registry AS R1, Registry AS R2;

세 행을 각각 세 행과 조합하므로 총 행이 만들어진다

R1.UserIDR1.CarR2.UserIDR2.Car
123Civic123Civic
123Civic567Civic
123Civic567Ferrari
567Civic123Civic
567Civic567Civic
567Civic567Ferrari
567Ferrari123Civic
567Ferrari567Civic
567Ferrari567Ferrari

같은 행끼리의 조합도 포함되며, (Civic, Ferrari)와 (Ferrari, Civic)처럼 순서가 반대인 조합도 각각 생성된다. 같은 테이블을 세 번 참조하면 행의 세 항목 조합을 만들 수도 있다. 행이 n개인 테이블을 조건 없이 k번 참조하면 조합 수는 n^k이다. 이는 논리적인 조합 수이며, DBMS가 항상 모든 조합을 실제로 생성한 뒤 필터링한다는 뜻은 아니다

3.3 같은 사람이 등록한 차량의 쌍 찾기

먼저 UserID가 같은 행만 조합한다

SELECT R1.UserID, R1.Car AS Car1, R2.Car AS Car2
FROM Registry AS R1
JOIN Registry AS R2
    ON R1.UserID = R2.UserID;
UserIDCar1Car2
123CivicCivic
567CivicCivic
567CivicFerrari
567FerrariCivic
567FerrariFerrari

서로 다른 차량 이름을 가진 쌍만 남기려면 비교 조건을 추가한다

SELECT R1.UserID, R1.Car AS Car1, R2.Car AS Car2
FROM Registry AS R1
JOIN Registry AS R2
    ON R1.UserID = R2.UserID
WHERE R1.Car <> R2.Car;
UserIDCar1Car2
567CivicFerrari
567FerrariCivic
R1.Car <> R2.Car는 차량 이름이 다른 쌍을 선택한다. 서로 다른 행인지 판별하는 조건과는 다르다. 같은 이름의 차량이 여러 행에 등록되어 있으면 그 쌍도 제외되며, 차량 이름이 NULL인 쌍은 비교 결과가 UNKNOWN이므로 제외된다

3.4 직원 이름 연결하기

Registry의 두 참조를 Payroll과 연결하면 직원 이름도 조회할 수 있다

SELECT P.Name, R1.Car AS Car1, R2.Car AS Car2
FROM Payroll AS P
JOIN Registry AS R1
    ON P.UserID = R1.UserID
JOIN Registry AS R2
    ON P.UserID = R2.UserID
WHERE R1.Car <> R2.Car;
NameCar1Car2
MagdaCivicFerrari
MagdaFerrariCivic
이 쿼리는 서로 다른 이름의 차량을 등록한 직원과 차량 쌍을 조회한다. 아직 Civic과 Ferrari라는 특정 조합으로 제한한 것은 아니다

3.5 Civic과 Ferrari 모두 등록한 직원 찾기

각 참조의 역할을 정하여, R1에서는 Civic을 찾고 R2에서는 Ferrari를 찾는다

SELECT P.Name, R1.Car AS Car1, R2.Car AS Car2
FROM Payroll AS P
JOIN Registry AS R1
    ON P.UserID = R1.UserID
JOIN Registry AS R2
    ON P.UserID = R2.UserID
WHERE R1.Car = 'Civic'
  AND R2.Car = 'Ferrari';
NameCar1Car2
MagdaCivicFerrari
두 차량 등록 행은 같은 직원에게 연결되지만, 서로 다른 별칭으로 참조된다. 따라서 한 행의 값이 동시에 두 차량 이름이어야 하는 문제가 해결된다

3.6 반대 순서의 쌍과 중복 처리

R1.Car <> R2.Car는 (Civic, Ferrari)와 (Ferrari, Civic)을 모두 남긴다. 두 결과는 열 값의 순서가 다른 행이므로, DISTINCT만으로 하나로 합쳐지지 않는다

차량 이름의 조합을 순서에 관계없이 한 번씩만 조회하려면 이름 사이의 정렬 순서를 이용할 수 있다

SELECT DISTINCT P.UserID, P.Name,
       R1.Car AS Car1, R2.Car AS Car2
FROM Payroll AS P
JOIN Registry AS R1
    ON P.UserID = R1.UserID
JOIN Registry AS R2
    ON P.UserID = R2.UserID
WHERE R1.Car < R2.Car;

<는 두 방향 중 한쪽만 선택하고, DISTINCT는 같은 출력 값이 반복되는 경우를 제거한다. 문자열의 비교 순서는 DBMS의 정렬 규칙에 따른다. 차량 이름이 아닌 개별 차량 행의 쌍을 구분하려면 차량 등록 번호 등 고유한 키를 비교해야 한다. 예를 들어 R1.RegistryID < R2.RegistryID를 사용하면 같은 차량 이름을 가진 서로 다른 행도 쌍으로 선택할 수 있다





4. 집합과 다중집합 연산

다중집합(bag, Multiset)

중복 원소를 허용하며, 각 원소가 몇 번 나타나는지도 의미를 가지는 집합이다

예를 들어 집합에서 {A, A, B}는 {A, B}와 같지만, 다중집합에서는 A가 두 번 나타나므로 서로 다르다

관계 모델의 관계는 집합을 기반으로 한다. 반면 SQL의 일반적인 SELECT 결과는 중복 행을 허용하므로 다중집합의 의미를 따른다. 중복 여부는 선택한 열 전체의 값으로 판단한다

4.1 집합 연산: UNION, INTERSECT, EXCEPT

UNION, INTERSECT, EXCEPT는 두 쿼리의 결과를 합집합, 교집합, 차집합으로 결합한다. ALL을 붙이지 않으면 결과의 중복 행을 제거한다

두 입력이 다음과 같다고 하자

  • Y = {A, B, C}
  • Z = {B, C, D}
연산의미결과
Y UNION Z어느 한쪽에라도 있는 원소{A, B, C, D}
Y INTERSECT Z양쪽에 공통으로 있는 원소{B, C}
Y EXCEPT ZY에는 있지만 Z에는 없는 원소{A}
Y EXCEPT Z와 Z EXCEPT Y는 일반적으로 결과가 다르다. 위 예제에서 Y EXCEPT Z는 {A}, Z EXCEPT Y는 {D}이다

4.2 다중집합 연산: ALL

UNION ALL, INTERSECT ALL, EXCEPT ALL은 중복 행의 등장 횟수를 반영한다. 각 연산에서 등장 횟수를 계산하는 방식은 서로 다르다

원소 e가 Y에서 m번, Z에서 n번 나타난다면 결과에서의 등장 횟수는 다음과 같다

연산결과에서 e의 등장 횟수
Y UNION ALL Zm + n
Y INTERSECT ALL Zmin(m, n)
Y EXCEPT ALL Zmax(0, m - n)

4.2.1 UNION ALL

양쪽 결과를 그대로 이어 붙이므로 등장 횟수를 더한다

  • Y = {A, A, B, B, C}
  • Z = {B, B, B, C, D, D}
  • 결과 = {A, A, B, B, B, B, B, C, C, D, D}

B는 Y에 2번, Z에 3번 있으므로 결과에 5번 나타난다

4.2.2 INTERSECT ALL

양쪽에서 서로 대응시킬 수 있는 개수만 남기므로 등장 횟수의 최솟값을 사용한다

  • Y = {A, A, B, B, C}
  • Z = {B, B, C, C, C, C, D, D}
  • 결과 = {B, B, C}

B는 양쪽에 2번씩 있어 2번 남고, C는 각각 1번과 4번 있어 1번 남는다

4.2.3 EXCEPT ALL

왼쪽의 등장 횟수에서 오른쪽의 등장 횟수를 뺀다. 결과가 음수이면 0으로 처리한다

  • Y = {A, A, B, B, C, C}
  • Z = {B, C, C, C, D, D}
  • 결과 = {A, A, B}

A는 오른쪽에 없어 2번 모두 남는다. B는 2 - 1 = 1번 남고, C는 max(0, 2 - 3) = 0번 남는다

EXCEPT는 오른쪽에도 존재하는 행을 결과에서 제외한다. EXCEPT ALL은 오른쪽에 나타난 횟수만큼 왼쪽의 행을 제거한다. 따라서 위 예제에서 EXCEPT의 결과는 {A}, EXCEPT ALL의 결과는 {A, A, B}이다

4.3 SQL에서 쿼리 결과 결합하기

집합 연산자는 SELECT 쿼리 사이에 작성한다

SELECT name, color
FROM T1
UNION
SELECT name, color
FROM T2;

두 쿼리에서 얻은 (name, color) 행을 합치고 중복을 제거한다. 중복도 유지하려면 UNION ALL을 사용한다

SELECT name, color
FROM T1
EXCEPT ALL
SELECT name, color
FROM T2;

이 쿼리는 T1에서 얻은 각 (name, color) 행의 등장 횟수에서 T2에서 얻은 동일한 행의 등장 횟수를 뺀다

결합할 쿼리의 열 구성

두 쿼리는 반환하는 열의 개수가 같아야 하며, 같은 위치의 열은 서로 호환되는 자료형이어야 한다. 열 이름으로 대응시키는 것이 아니라 열의 위치에 따라 대응시킨다

예를 들어 SELECT name, color와 SELECT color, name을 결합하면 이름끼리, 색상끼리 자동으로 맞춰지지 않는다

4.4 중복이 집계 결과에 미치는 영향

SQL은 조회나 조인 과정에서 중복 행을 만들 수 있다. 중복은 집계 결과에도 영향을 주므로 무조건 제거해서는 안 된다

예를 들어 조회된 값이 10, 10, 20이면 다음과 같다

계산 방식행 수합계평균
중복을 포함340약 13.33
중복을 제거한 뒤 계산23015

같은 값이 여러 번 나타나는 것이 서로 다른 거래나 기록을 의미한다면, 중복 제거는 필요한 정보까지 없앨 수 있다

그래프 뷰