[데이터베이스시스템및응용] 3. JOIN

1. JOIN

조인(JOIN)

둘 이상의 테이블에서 조인 조건을 만족하는 행들을 결합하여 데이터를 조회하는 연산이다

하나의 테이블에 필요한 정보가 모두 들어 있지 않다면, 여러 테이블의 데이터를 연결해야 한다

예들 들어 직원의 이름이 Payroll에, 직원이 소유한 차량이 Registry에 저장되어 있다면, 두 테이블을 연결해야 직원 이름과 차량을 함께 조회할 수 있다

1.1 외래키와 JOIN의 차이

구분	역할
외래키(Foreign Key)	참조하는 값이 참조 대상의 키에 존재하도록 제약하여 참조 무결성을 유지한다
조인(Join)	질의에서 지정한 조건에 따라 여러 테이블의 행을 결합한다

외래키는 테이블 사이의 관계를 제약 조건으로 나타내고, JOIN은 관련된 데이터를 실제 질의 결과로 결합한다

• 외래키를 이용해 연결된 테이블을 JOIN할 수 있다
• 외래키가 선언되어 있지 않아도 JOIN할 수 있다
• JOIN 조건은 반드시 기본키와 외래키 사이의 비교일 필요가 없다

외래키를 선언했다고 조회 결과가 자동으로 결합되는 것은 아니다. 여러 테이블의 데이터를 함께 조회하려면 질의에 JOIN을 작성해야 한다

예제 테이블

Payroll: 직원 정보

UserID	Name	Job	Salary
123	Leslie	TA	50000
345	Frances	TA	60000
567	Magda	Prof	120000
789	Quinn	Prof	100000

Registry: 차량 등록 정보

UserID	Car
123	Charger
567	Civic
567	Ferrari

Payroll.UserID와 Registry.UserID의 값이 같은 행을 연결하면 차량 소유자의 이름과 직업, 급여를 알 수 있다

1.2 내부 조인

내부 조인(Inner Join)

조인 조건을 만족하는 양쪽 테이블의 행 조합만 결과에 포함하는 조인이다

내부 조인은 SQL에서 가장 기본적으로 사용하는 조인이다. SQL 구문에서 INNER JOIN은 JOIN 으로 줄여 쓸 수 있다

조인 조건(Join Predicate)

서로 다른 테이블의 어떤 행들을 결합할지 결정하는 조건이다

위 예제의 조인 조건은 다음과 같다

• P.UserID = R.UserID

두 테이블에서 UserID가 같은 행을 결합하여 이름, 직업, 급여, 차량을 출력하면 다음 결과를 얻는다

Name	Job	Salary	Car
Leslie	TA	50000	Charger
Magda	Prof	120000	Civic
Magda	Prof	120000	Ferrari

내부 조인은 조건을 만족하는 모든 행 조합을 반환한다. 한 행이 상대 테이블의 여러 행과 일치하면, 일치하는 조합마다 결과 행이 생성된다

조인은 양쪽 행의 값을 결합하는 연산이므로, 동일한 행만 찾는 수학적 집합의 교집합과는 구분한다

1.3 내부 조인 작성 방법

1.3.1 명시적 조인: INNER JOIN … ON

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

구문	역할
FROM Payroll AS P	Payroll을 사용하고 P라는 별칭을 붙인다
INNER JOIN Registry AS R	Registry를 내부 조인하고 R이라는 별칭을 붙인다
ON P.UserID = R.UserID	두 테이블에서 UserID가 같은 행을 결합한다.
SELECT P.Name, R.Car	결합된 행에서 직원 이름과 차량을 출력한다
ON은 행들을 결합할 조인 조건을 지정한다

1.3.2 INNER 생략

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

JOIN만 작성하면 내부 조인을 의미하므로, 앞의 INNER JOIN 질의와 같은 결과를 얻는다

1.3.3 암시적 조인: FROM … WHERE

SELECT P.Name, R.Car
FROM Payroll AS P, Registry AS R
WHERE P.UserID = R.UserID;

• FROM에 여러 테이블을 쉼표로 구분하여 나열한다
• WHERE에 조인 조건을 작성한다
• 이 예제에서는 앞의 두 명시적 조인 질의와 같은 결과를 얻는다

작성 방식	테이블 결합 표현	조인 조건의 위치
명시적 조인	INNER JOIN 또는 JOIN	ON
암시적 조인	FROM에 테이블을 쉼표로 나열	WHERE

세 질의 모두 결과에 (Leslie, Charger), (Magda, Civic), (Magda, Ferrari)를 포함한다. 단, ORDER BY가 없으므로 결과 행의 표시 순서는 보장되지 않는다

1.4 자연 조인: NATURAL JOIN

자연 조인(NATURAL JOIN)

양쪽 테이블에서 이름이 같은 모든 열을 등호로 비교하여 조인한다. INNER, LEFT 등의 종류를 따로 지정하지 않은 NATURAL JOIN은 내부 조인이다

다음 두 테이블의 공통 열 이름이 sid뿐이라고 하자

• Sailors(sid, sname, rating, age)
• Reserves(sid, bid, day)

SELECT S.sid, S.sname, R.bid
FROM Sailors AS S
NATURAL JOIN Reserves AS R
WHERE S.age > 20;

이 스키마에서는 다음과 같은 의미이다

SELECT S.sid, S.sname, R.bid
FROM Sailors AS S
JOIN Reserves AS R
    ON S.sid = R.sid
WHERE S.age > 20;

1.4.1 SELECT *에서의 공통 열

NATURAL JOIN의 SELECT *는 공통 열을 한 번만 출력한다. 위 예제에서는 sid가 하나만 나타난다. 반면 JOIN ... ON의 SELECT *는 양쪽 sid 열을 모두 출력한다. 조인 기준 열을 지정하면서 공통 열을 한 번만 출력하려면 USING을 사용할 수도 있다

SELECT *
FROM Sailors
JOIN Reserves USING (sid);

USING

USING (열 목록)은 양쪽 테이블에서 지정한 같은 이름의 열들을 등호로 비교하다. 자연 조인과 달리 조인에 사용할 공통 열을 직접 지정한다

1.4.2 자연 조인 사용 시 주의점

공통 열 이름에 의존하는 조인 조건

두 테이블에 같은 이름의 열이 추가되면 자연 조인은 새 공통 열도 조인 조건에 포함하므로 결과가 달라질 수 있다. 조인 기준을 명확하게 유지하려면 ON이나 USING으로 열을 지정하는 편이 좋다

예를 들어 두 테이블에 모두 status가 추가되면 자연 조인은 다음 두 조건을 모두 요구한다

ON S.sid = R.sid
AND S.status = R.status

공통 열 이름이 하나도 없으면 자연 조인은 모든 행의 조합을 만든다. 자연 조인이 외래키를 찾아 자동으로 연결하는 것은 아니다

---

2. 조인 결과의 필터링

조인으로 결합한 행에도 WHERE 조건을 적용할 수 있다. 내부 조인은 조인 조건을 만족하는 행들을 결합한 뒤, 추가 조건에 맞는 행을 선택하는 것으로 이해할 수 있다. 이는 질의의 논리적 의미이며, 실제로 임시 테이블을 만들거나 반드시 이 순서대로 실행한다는 뜻은 아니다

예제 테이블 Payroll은 앞의 예제와 같지만, 이번 예제의 Registry는 다음과 같이 바뀐다

UserID	Car
123	Civic
567	Civic
567	Ferrari
789	Picklemobile

두 테이블에서 UserID가 같은 행을 결합하면 다음 정보를 얻는다

Name	Job	Salary	Car
Leslie	TA	50000	Civic
Magda	Prof	120000	Civic
Magda	Prof	120000	Ferrari
Quinn	Prof	100000	Picklemobile

2.1 명시적 조인과 WHERE

Civic 또는 Ferrari를 소유한 직원의 이름을 조회한다

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

조회 결과

Name
Leslie
Magda
Magda

Magda는 Civic과 Ferrari를 모두 소유하므로 조건을 만족하는 조합이 두 개이다. 이름만 출력하더라도 일반 SELECT는 중복을 제거하지 않으므로 두 행이 남는다. 직원 이름의 중복을 제거하려면 DISTINCT를 이용한다

다만 이름이 같은 서로 다른 직원도 하나로 합쳐질 수 있으므로, 직원 자체를 구분하려면 사용자 ID도 함께 조회하는 것이 적절하다

2.2 암시적 조인과 WHERE

같은 질의를 암시적 조인으로 작성하면 다음과 같다

SELECT P.Name
FROM Payroll AS P, Registry AS R
WHERE P.UserID = R.UserID
  AND (R.Car = 'Civic' OR R.Car = 'Ferrari');

WHERE에는 두 종류의 조건이 함께 들어 있다. 조인 조건과 차량 조건을 모두 만족해야 하므로 AND로 연결한다. 차량 조건은 둘 중 하나만 만족하면 되므로 OR로 연결한다

이를 의사코드로 표현하면 다음과 같다

Payroll의 각 행 p에 대해:
    Registry의 각 행 r에 대해:
        만약 p.UserID와 r.UserID가 같다면:
            만약 r.Car가 Civic 또는 Ferrari라면:
                p.Name을 출력한다

2.3 AND와 OR의 우선순위

SQL에서 AND는 OR보다 먼저 적용된다. 괄호를 사용하면 조건을 묶는 방식을 명확하게 지정할 수 있다

다음 두 조건은 의미가 다르다

의도한 조건

WHERE P.UserID = R.UserID
	AND (R.Car = 'Civic' OR R.Car = 'Ferrari')

직원의 UserID와 차량 등록 행의 UserID가 같으면서, 차량이 Civic 또는 Ferrari여야 한다

괄호를 생략한 조건

WHERE P.UserID = R.UserID
  AND R.Car = 'Civic'
   OR R.Car = 'Ferrari'

AND가 먼저 적용되므로 다음과 같이 해석된다

WHERE (P.UserID = R.UserID AND R.Car = 'Civic')
   OR R.Car = 'Ferrari'

이 경우 차량이 Ferrari이면 UserID가 일치하지 않아도 조건 전체가 참이 된다

AND와 OR를 함께 사용할 때는 의도한 조건 묶음을 괄호로 명확하게 표현해야 한다

---

3. 조인의 실행과 최적화

3.1 필터 조건을 미리 적용

Ferrari를 소유한 직원의 이름을 조회한다고 하자

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

R.Car = 'Ferrari'는 Registry의 값만으로 판단할 수 있다. 따라서 DBMS는 조인 전에 Ferrari 등록 행만 선택하여 비교할 행의 수를 줄일 수 있다

처리 방법	직원과 차량의 UserID 비교 횟수
모든 직원과 차량 등록 행의 조합 비교	$4\times 4=16$
Ferrari 등록 행 1개를 먼저 선택한 뒤 비교	$4\times 1=4$

단, Ferrari 등록 행을 찾기 위한 필터 검사 비용은 별도로 든다. 조건을 미리 검사할 수 있다면, 불필요한 행 조합을 줄이면서 같은 결과를 얻을 수 있다

3.2 인덱스 활용

인덱스(Index)

특정 속성값을 가진 데이터를 효율적으로 찾도록 돕는 자료구조이다

Registry.Car에 적절한 인덱스가 있다면, DBMS는 모든 차량 등록 행을 순회하는 대신 인덱스를 이용해 Ferrari에 해당하는 행을 찾을 수 있다

방법	처리 방식
전체 순회	각 행의 Car 값을 검사하여 Ferrari 등록 행을 찾는다.
인덱스 활용	인덱스에서 Ferrari에 해당하는 행을 찾아 읽는다.

인덱스가 있다고 항상 사용하는 것은 아니다. DBMS는 데이터의 양과 조건을 만족하는 행의 수 등을 고려하여 실행 방법을 선택한다