SQL 테이블 열연결(JOIN)

서로 다른 테이블을 가로 방향으로 합쳐보자.

JOIN

select rating.user_id, rating.rating, ramen.name, ramen.quantity, ramen.is_spicy
from rating, ramen
where ramen.id = rating.item_id and rating.item_type = 'ramen';

위 쿼리는 아래와 같이 사용가능하다

select rating.user_id, rating.rating, ramen.name, ramen.quantity, ramen.is_spicy
from ( 
    rating join ramen 
    on ramen.id = rating.item_id and rating.item_type = 'ramen'
);

두 쿼리문의 성능차이는 전혀 없다. JOIN 사용이 가독성이 더 좋기때문에 테이블 열결합시 더 자주 사용한다.

JOIN의 종류

내부JOIN은 두 테이블값이 서로 연결되는 부분만 결과로 출력,
외부JOIN은 서로 연결되지 않고 한쪽만 데이터가 있는 행도 출력

명령어	축약형	내부/외부 구분
INNER JOIN	JOIN	내부
LEFT OUTER JOIN	LEFT JOIN	외부
RIGHT OUTER JOIN	RIGHT JOIN	외부
FULL OUTER JOIN	FULL JOIN	외부

# JOIN
두 테이블값이 서로 연결되는 부분만 결과로 출력
select rating.user_id, rating.rating, ramen.name, ramen.quantity, ramen.is_spicy
from ( 
    rating join ramen 
    on ramen.id = rating.item_id and rating.item_type = 'ramen'
);
 user_id | rating |    name    | quantity | is_spicy
---------+--------+------------+----------+----------
       2 |      1 | 고추짜장면 |       15 | f
       9 |      3 | 겨울이라면 |       50 | t
       6 |      4 | 겨울이라면 |       50 | t
       2 |      4 | 진라멘     |        2 | f
       8 |      3 | 고추짜장면 |       15 | f
                    (이하생략)
(11 rows)

# LEFT JOIN
앞 테이블 기준으로 뒤 테이블과 연결되는 정보만 출력, 만약 없다면 NULL 값으로 출력
select rating.user_id, rating.rating, ramen.name, ramen.quantity, ramen.is_spicy
from (
    rating left join ramen
    on ramen.id = rating.item_id and rating.item_type = 'ramen'
);
 user_id | rating |    name    | quantity | is_spicy
---------+--------+------------+----------+----------
       2 |      1 | 고추짜장면 |       15 | f
      10 |      4 |            |          |
       9 |      3 | 겨울이라면 |       50 | t
       5 |      3 |            |          |
       2 |      4 | 진라멘     |        2 | f
       8 |      4 |            |          |
       8 |      3 | 고추짜장면 |       15 | f
       4 |      1 |            |          |
      10 |      2 |            |          |
                    (이하생략)
(20 rows)

# RIGHT JOIN
뒤 테이블 기준으로 앞 테이블과 연결되는 정보만 출력, 만약 없다면 NULL 값으로 출력
select rating.user_id, rating.rating, ramen.name, ramen.quantity, ramen.is_spicy
from (
    rating right join ramen
    on ramen.id = rating.item_id and rating.item_type = 'ramen'
);
 user_id | rating |    name    | quantity | is_spicy
---------+--------+------------+----------+----------
       8 |      3 | 고추짜장면 |       15 | f
       2 |      1 | 고추짜장면 |       15 | f
         |        | 리얼짬뽕   |       47 | t
       4 |      1 | 추풍라면   |       18 | f
                    (이하생략)
(12 rows)

# FULL JOIN
서로 연결하여 출력, 서로 연결되지 않은 행은 NULL 값으로 출력
select rating.user_id, rating.rating, ramen.name, ramen.quantity, ramen.is_spicy
from (
    rating full join ramen
    on ramen.id = rating.item_id and rating.item_type = 'ramen'
);
 user_id | rating |    name    | quantity | is_spicy
---------+--------+------------+----------+----------
       2 |      1 | 고추짜장면 |       15 | f
      10 |      4 |            |          |
       9 |      3 | 겨울이라면 |       50 | t
       5 |      3 |            |          |
         |        | 리얼짬뽕   |       47 | t
                     (이하생략)
(21 rows)

JOIN문이 사용되는 위치

FROM 다음에 사용한다.
2개의 join을 사용할경우 구조 파악을 위해, 괄호를 적으면 파악이 쉽다.

select rating.user_id, rating.rating, 
    ramen.name, ramen.quantity, ramen.is_spicy
from (
    (
        rating full join ramen
        on ramen.id = rating.item_id and rating.item_type = 'ramen'
    )  join users
    on users.id = rating.user_id
)
where rating >= 3;

ON, USING

테이블 join 테이블 on 연결할 조건
테이블 join 테이블 using (두 테이블을 연결할 공통된 컬럼 목록)

using의 경우 두 테이블을 연결하는 컬럼의 이름이 같아야함

# on
select drink_rating.user_id, drink_rating.rating,
    drink.name, drink.quantity, drink.volume
from (
    (
        select rating.item_id as id, rating.rating, rating.user_id
        from rating
        where rating.item_type = 'drink'
    ) drink_rating
    join drink
    on drink.id = drink_rating.id
);
 user_id | rating |     name     | quantity | volume
---------+--------+--------------+----------+--------
       7 |      1 | 에너지드링크 |       36 |    300
       5 |      3 | 더맛         |        3 |   1000
      10 |      4 | 더맛         |        3 |   1000
(3 rows)

# using
select drink_rating.user_id, drink_rating.rating,
drink.name, drink.quantity, drink.volume
from (
(
    select rating.item_id as id, rating.rating, rating.user_id
        from rating
        where rating.item_type = 'drink'
) drink_rating
    join drink
    using (id)
);
 user_id | rating |     name     | quantity | volume
---------+--------+--------------+----------+--------
       7 |      1 | 에너지드링크 |       36 |    300
       5 |      3 | 더맛         |        3 |   1000
      10 |      4 | 더맛         |        3 |   1000
(3 rows)