Chap05 - 조인, JOIN

Chap05 - 조인, JOIN

이전 포스팅에서는 T-SQL의 데이터 형식(타입)과 시스템함수에 대해 살펴보았다. 이번 포스팅에서는 SQL Server에서 제공하는 INNER JOIN 과 OUTER JOIN 그리고 UNION/EXCEPT/INTERSECT에 대해 살펴보도록 하자.

이번 포스팅에서 이루어지는 실습은 Chap03-1.Transact-SQL 기본에서 생성한 sqlDB를 사용하기 때문에, 먼저 해당 링크를 통해 sqlDB를 생성해야 한다.

Goals

• 조인(Join)이 무엇인지에 대해 알아본다.

• 조인의 종류 INNER/OUTER/CROSS/SELF 조인에 대해 알아본다.

• UNION/UNION ALL/EXCEPT/INTERSECT에 대해 알아본다.

1. 조인(Join)이란

조인(Join)이란 두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것을 말한다. 조인을 사용하면 테이블 간의 (논리적) 관계를 기준으로 둘 이상의 테이블에서 데이터를 검색할 수 있다. 조인은 Microsoft SQL Server에서 특정 테이블의 데이터를 사용하여 다른 테이블의 행을 선택하는 방법을 나타낸다([링크] 참고).

조인 조건은 아래와 같이 쿼리에서 두 테이블의 관계를 정의한다.

• 조인에 사용될 각 테이블에서 컬럼(열, column)을 지정한다. 일반적으로 조인 조건은 한 테이블에서 외래 키(FK, Foreign Key)를 지정하고 다른 테이블에서 이와 관련된 키(e.g. PK)를 지정한다.

• 컬럼에서 값을 비교할 때 사용할 논리 연산자(예: =, <>)를 지정한다.

조인의 개념에 대해 알아보았으니, 실습을 통해 조인의 종류를 살펴보도록 하자.

2. INNER JOIN (내부 조인)

INNER JOIN은 조인 중에서 가장 많이 사용되기 때문에, 일반적으로 JOIN이라고 하면 INNER JOIN을 가리킨다. INNER JOIN의 형식은 다음과 같다. 아래의 형식에서 INNER JOIN을 그냥 JOIN으로 써도 INNER JOIN으로 인식하여 실행된다.

SELECT <컬럼 목록>
FROM <첫 번째 테이블>
    INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색조건]

아래의 예제는 sqlDB의 buyTbl 테이블과 userTbl의 INNER JOIN을 통해 'JYP'라는 아이디를 가진 사람의 '이름/주소/연락처'를 검색하는 쿼리이다.

USE sqlDB;
SELECT buyTbl.userID, name, prodName, 
    addr, mobile1 + mobile2 AS [연락처]
FROM buyTbl
    INNER JOIN userTbl
    ON buyTbl.userID = userTbl.userID
WHERE buyTbl.userID = 'JYP';

위의 예제의 결과에 대한 과정은 다음과 같다.

1. buyTbl의 userID에서 'JYP'를 찾는다.

2. 그런다음, 'JYP'와 같은 값을 userTbl의 userID컬럼에서 검색한다.

3. 마지막으로 buyTbl과 userTbl 두 행(row)을 결합(JOIN)한다.

위의 쿼리에서 조회한 컬럼 목록들이 어떤 테이블에서 나왔는지 명확하게 해주기 위해서 다음의 쿼리처럼, 각 테이블에 별칭(Alias)을 이용하는 것이 좋다.

SELECT B.userID, U.name, B.prodName, 
    U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM buyTbl AS B
    INNER JOIN userTbl AS U
    ON B.userID = U.userID
WHERE B.userID = 'JYP';

3. OUTER JOIN (외부 조인)

OUTER JOIN은 조인의 조건에 만족하지 않는 행까지 포함시키는 조인 방법이다. OUTER JOIN에는 LEFT/RIGHT/FULL 조인이 있다.

• LEFT JOIN은 왼쪽(먼저 나타나는) 테이블에서 조인 조건에 맞지 않는 모든 행을 결과 집합에 포함시키고 내부 조인에서 반환된 모든 행과 오른쪽 테이블의 출력 열을 NULL로 설정하도록 지정한다.

• RIGHT JOIN은 오른쪽(나중에 나타나는) 테이블에서 조인 조건에 맞지 않는 모든 행을 결과 집합에 포함하고 내부 조인에서 반환된 모든 행과 다른 테이블에 해당되는 출력 열을 NULL로 설정하도록 지정한다.

• FULL JOIN은 조인 조건에 맞지 않는 왼쪽 또는 오른쪽 테이블의 행을 결과 집합에 포함시키고 다른 테이블에 해당되는 출력 열을 NULL로 설정하도록 지정한다. 여기에는 INNER JOIN에서 일반적으로 반환되는 모든 행도 포함된다.

아래의 예제 쿼리는 userTbl과 buyTbl을 각각 LEFT / RIGHT / FULL 조인 한 쿼리이다(결과 이미지 생략).

USE sqlDB;
​
-- LEFT JOIN
SELECT U.userID, U.name, B.prodName, 
    U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM userTbl as U
    LEFT JOIN buyTbl as B
    ON U.userID = B.userID
    
-- RIGHT JOIN
SELECT U.userID, U.name, B.prodName, 
    U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM userTbl as U
    RIGHT JOIN buyTbl as B
    ON U.userID = B.userID
    
-- FULL JOIN
SELECT U.userID, U.name, B.prodName, 
    U.addr, U.mobile1 + U.mobile2 AS [연락처]
FROM userTbl as U
    FULL JOIN buyTbl as B
    ON U.userID = B.userID

4. CROSS JOIN (상호 조인)

CROSS JOIN은 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 것을 말한다. 즉, 두 테이블의 교차곱을 의미하므로 CROSS JOIN의 결과 개수는 두 테이블의 개수를 곱한 값이 된다(그림 출처 : tutorial gateway).

아래의 쿼리는 buyTbl과 userTbl을 CROSS JOIN한 쿼리문이다.

SELECT *
FROM buyTbl
    CROSS JOIN userTbl;

CROSS JOIN은 교차곱을 하기 때문에 ON을 사용할 수 없다. CROSS JOIN은 테스트로 사용할 많은 용량의 데이터를 생성할 때 주로 사용한다고 한다.

5. SELF JOIN (자체 조인)

SELF JOIN은 별도의 구문이 있는 것이 아니라 자기 자신(테이블)과 조인한다는 의미이다(그림 출처 : tutorial gateway).

SELF JOIN을 사용하는 대표적인 예는 [Chap03-1. Transact-SQL 기본 - SELECT]에서 4.2 재귀적 CTE에서 살펴본 empTbl이다. 아래의 그림에서 예를들어 '이부장'의 경우에는 직원이기 때문에 직원(EMP)컬럼에 있는 동시에, '우대리'와 '지사원'의 상관이기도 해서 상관(MANAGER) 컬럼에도 존재한다.

이렇게, 하나의 테이블에 같은 데이터가 있되 의미는 다른 경우에는, 두 테이블을 서로 SELF JOIN시켜서 정보를 확인할 수 있다. 아래의 쿼리는 '우대리' 상관의 부서를 확인하는 쿼리이다.

USE sqlDB;
SELECT A.emp AS [부하직원], B.emp AS [직속상관], B.department AS [직속상관부서]
FROM empTbl A
    INNER JOIN empTbl B
    ON A.manager = B.emp
WHERE A.emp = '우대리';

6. UNION/UNION ALL/EXCEPT/INTERSECT

6.1 UNION/UNION ALL

UNION은 두 쿼리의 결과를 행으로 합치는 것을 말한다. 즉, 합집합을 의미한다(그림 출처: essentialsql).

SELECT 문장1
    UNION [ALL]
SELECT 문장2

UNION ALL을 사용하면 중복된 컬럼까지 모두 출력 된다. UNION을 사용할 때는 다음과 같은 규칙을 지켜야 한다.

• 컬럼의 개수와 순서가 모든 쿼리에서 동일해야 한다.

• 데이터 형식이 서로 호환되어야 한다.

아래의 쿼리는 sqlDB의 changeTBL과 userTbl을 이용해 UNION을 실행한 예이다.

USE sqlDB;
SELECT userID, name FROM userTbl
    UNION ALL
SELECT userID, name FROM changeTBL;

6.2 EXCEPT

EXCEPT는 두 번째 쿼리에 해당하는 것을 제외하는 구문이다. 즉, 차집합A - B을 의미한다.

아래의 쿼리는 sqlDB의 userTbl에서 전화번호가 없는 사람을 제외하는 쿼리이다.

USE sqlDB;
SELECT name, mobile1+mobile2 AS [전화번호] FROM userTbl
    EXCEPT
SELECT name, mobile1+mobile2 FROM userTbl WHERE mobile1 IS NULL;

6.3 INTERSECT

INTERSECT는 교집합(​)을 의미한다.

다음의 예제는 userTbl과 buyTbl에서 공통으로 나타나는userID를 INTERSECT를 통해서 조회하는 예제이다.

USE sqlDB;
SELECT userID FROM userTbl
    INTERSECT
SELECT userID FROM buyTbl;

7. 마무리

이번 포스팅에서는 INNER JOIN 과 OUTER JOIN 그리고 UNION/EXCEPT/INTERSECT에 대해 알아보았다. 다음 포스팅에서는 SQL 프로그래밍에 대해 알아보도록 하자.