[SQL - Join (3)] SQL의 조인 문들 (Inner조인, Left/Right 조인, Full Join, Natural Join 등 + 여러개의 조인 문 중첩하는 법)

[Database]/SQL

[SQL - Join (3)] SQL의 조인 문들 (Inner조인, Left/Right 조인, Full Join, Natural Join 등 + 여러개의 조인 문 중첩하는 법)

quokkalover 2022. 4. 4. 20:41

현재 SQL의 조인 시리즈를 작성하고 있다. Join시리즈의 목차를 참고하려면 이 글을 참고하길 바란다.

앞 글에서 SQL의 Join을 어떻게 분류하는지에 대해 살펴봤다면, 본 글은 SQL의 다양한 Join문들을 소개하고 이들에 대한 예시를 다룬다.

본 글은 다음의 Join문들을 다룬다.

Inner Join (조인)
Outer Join (Left, Right Outer Join)
Full Join (Union)
Cross Join (Cartesian Join)
Self Join
Multiple Joins (여러개의 Join문을 중첩하는 법)
Natural Join(Equi Join의 또 다른 종류)

참고로 EXISTS와 관련있는 Semi-Join과 Anti-Join은 다음 글에서 EXISTS문과 함께 자세히 다룬다.

Inner Join

내부조인은 가장 많이 사용되는 조인 구문 중에 하나다. 내부 조인은 조인 Condition에 따라 2 개의 테이블(A, B)의 컬럼을 합쳐 새로운 테이블을 생성한다. 즉 교차 조인을 한 결과에 조인 조건문을 충족시키는 레코드를 반환한다고 생각할 수 있다.

내부 조인을 벤 다이어그램으로 표현하면 아래와 같다.

내부 조인은 가장 흔하게 사용되는 조인 종류다.

바로 예를 들어 설명해보겠다.

위와 같은 데이터가 있다고 했을때, users테이블과 addresses테이블에서 user_id가 일치하는 row들에 대해 Inner Join을 수행하려면 아래와 같이 쿼리를 입력해 실행하면된다.

SELECT users.*, addresses.*
       FROM users
       INNER JOIN addresses
             ON users.id = addresses.user_id;

결과로는

위 예시에서는 addresses테이블에 user_id가 5가 없기 때문에 Jane Smith가 제외됐다.

Jane Smith도 포함하기 위해서는 Left Outer Join을 수행해야 한다.

Outer Join

Left Join (Left Outer Join)

조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 우측 테이블에서 JOIN 대상 데이터를 읽어온다. 우측 테이블에서 만족하는 데이터가 없는 경우 NULL값으로 채운다

SELECT users.*, addresses.*
       FROM users
       LEFT JOIN addresses
            ON users.id = addresses.user_id;

위 예제에서 사용한 데이터에서 위 쿼리를 실행할 경우

Jane Smith에 관련된 address table의 정보는 없기 때문에 모두 null로 채워진다.

Right Join (Right Outer Join)

Right Join은 Left Join의 반대다. 오른쪽 테이블을 중심으로 왼쪽 테이블을 매치시킨다. 방향만 바꾼 것이므로 해당 레코드가 여러번 표시되거나 Null이 표시된다.

이번엔 다른 데이터로 한번 Right Join을 해보자

위와 같은 데이터가 있을 때

아래처럼 Right Join 쿼리를 보내게 되면

SELECT reviews.book_id, reviews.content,
       reviews.rating, reviews.published_date,
       books.id, books.title, books.author
    FROM reviews
    RIGHT JOIN books
          ON reviews.book_id = books.id;

다음과 같이 데이터가 리턴된다

Full Join

Full Join은 Full Outer Join이라고도 표현한다. 참고로 Full Join은 지원하지 않는 DB가 많기 때문에, UNION함수를 사용해서 Full Outer Join을 대체하여 사용하는 경우가 많다.

Full Outer Join은 이름에서알 수 있듯이, 양쪽 테이블에 있는 데이터 중 빈 값은 모두 Null로 채운 뒤 모든 데이터를 보여준다.

예를 들면 아래와 같은 데이터가 있을 때

다음과 같이 Full Outer Join을 수행하면

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

아래과 같은 결과가 출력된다.

Cross Join (Caretesian Join)

Cross Join은 Cartesian Join으로도 불리는 Join으로, 두 테이블의 데이터의 모든 조합을 받아온다. 다시 말해 한쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인시키는 기능을 한다.

따라서 Cross Join의 결과갯수는 두 테이블의 행의 개수를 곱한 갯수가 된다.

아래 예시를 보자. 왼쪽 테이블의 데이터 1개당 오른쪽 데이터를 처음부터 끝까지 하나씩 결합한다. 그리고 왼쪽의 데이터가 없어질 때까지 반복한다.

CROSS Join절의 예시는 아래와 같다.

SELECT (조회 컬럼)
FROM (테이블 명)
CROSS JOIN Orders ON (조인 조건)

On 절을 지정할 수도 있지만 CROSS Join절은 조인 조건을 지정하지 않고 사용하는 경우가 많다.

SELECT * FROM users CROSS JOIN addresses;

Self Join (셀프 조인)

셀프 조인은 자기 자신과 조인하는 조인이다.

하나의 테이블을 여러번 복사해서 조인한다고 생각하면된다. 자신이 가지고 있는 칼럼을 다양하게 변형시켜 활용할 경우에 자주 사용한다

예를 들면 같은 부서에서 일하는 직원을 알고 싶을 때 셀프 조인을 사용할 수 있다.

--문법--
SELECT
테이블별칭.조회할칼럼,
테이블별칭.조회할칼럼
FROM 테이블 별칭,테이블 별칭2

--예제--
SELECT
A.NAME, --A테이블의 NAME조회
B.AGE --B테이블의 AGE조회
FROM EX_TABLE A,EX_TABLE B

조인 결과

Multiple Joins

SQL을 사용하다보면 2개 이상의 테이블을 조인해야 하는 경우가 생긴다.

이런 경우에는 JOIN Clause를 뒤에 이어 붙이는 형태로 할 수 있다.

예를 들어보자

SELECT users.full_name, books.title,
       checkouts.checkout_date
    FROM users
    INNER JOIN checkouts
          ON users.id = checkouts.user_id
    INNER JOIN books
          ON books.id = checkouts.book_id;

위와 같은 형태로 조인문을 수행할 경우에는 어떻게 조인문이 수행될까?

위와 같이 쿼리를 수행하게되면 SQL은 FIRST JOIN을 수행한 결과를 담은 virtual table을 하나 생성한다(transient table이라고도 부름)

그러고 이 virtual table과 두 번째 테이블인 books 테이블과의 Inner Join이 수행된다.

이때 FROM이 필요없는 이유는 직전의 virtual table을 자동으로 refer하기 때문이다.

뒤에 세 번째 네 번째 JOIN문이 붙을때도 똑같이 동작한다. 직전의 virtual 테이블과의 Join연산이 수행된다.

따라서 위 예시를 설명으로 해보면

users과 checkouts테이블을 조인한 virtual table A가 생성되고,
두번째 join문은 앞에서 생성된 virtual table A와 books테이블의 컬럼을 조인하고 그중 title 컬럼을 출력한다.
따라서 위 쿼리를 호출해보면 users.full_name, boks.title, checkouts.checkout_date컬럼 세개를 가진 join이 수행된 결과가 출력된다.