mySQL 테이블 조인

2022-02-13

Data Base / SQL / mySQL

조인(join): 여러 테이블을 합쳐서 하나의 테이블인 것처럼 보는 행위

Foreign Key (외래키)

다른 테이블의 특정 row를 식별할 수 있게 해주는 컬럼.
Foreign Key는 다른 테이블의 특정 row를 식별할 수 있어야 하기 때문에 주로 다른 테이블의 Primary Key를 참조할 때가 많다.
foreign key를 지정해주는 이유: 지정했을 때 이상한 id값 추가를 시도하면 mySQL에서 에러를 발생시킨다.
자식 테이블: 참조를 하는 테이블
부모 테이블: 참조를 당하는 테이블

다른 종류의 테이블 조인하기

연결하다, 합치다라는 의미.
보통 Foreign Key를 기준으로 하는 것이 맞지만, 서로 같은 의미를 나타내는 컬럼들을 기준으로 조인하기도 한다.

LEFT OUTER JOIN / RIGHT OUTER JOIN

LEFT OUTER JOIN: 왼쪽에 있는 테이블을 기준으로 오른쪽에 있는 테이블을 합치라는 의미.
RIGHT OUTER JOIN: 오른쪽에 있는 테이블을 기준으로 왼쪽에 있는 테이블을 합치라는 의미.
ON으로 시작하는 구문: 두 테이블을 합칠 때 item.id의 값과 stock.item_id의 값을 비교하여 같은 값끼리 가로 방향으로 연결하라.
item row에는 있지만 stock row에는 없을 경우 합쳐질 때 null로 표시된다.

예시

SELECT
    item.id,
    item.name,
    stock.item_id,
    stock.inventory_count
FROM item LEFT OUTER JOIN stock
ON item.id = stock.item_id

INNER JOIN

INNER JOIN: 테이블의 일치하는 값이 있는 컬럼들로만 합쳐 연결된다. 기준이 되는 테이블이 따로 없으며 기준 테이블이 없으므로 null이 되는 경우는 없다.

alias 붙이기

AS로 alias를 붙여준다.
스페이스로 구분하여 이름을 붙여줘도 괜찮다.

SELECT
    i.id,
    i.name,
    s.item_id,
    s.inventory_count
FROM item AS i LEFT OUTER JOIN stock AS s
ON i.id = s.item_id

컬럼의 alias와 테이블의 alias

컬럼의 alias: 각 컬럼이 실제로 우리에게 그 alias로 변환되어서 보여지게 하기 위한 용도
테이블의 alias: SQL 문의 전체 길이를 줄여서 가독성을 높이기 위해 사용. 한번 alias를 붙였으면 그 테이블은 해당 alias로만 나타내야 한다.

결합 연산과 집합 연산

연산: 테이블을 합치는 작업
결합 연산: 테이블을 가로 방향으로 합치는 것에 관한 연산
집합 연산: 테이블을 세로 방향으로 합치는 것에 관한 연산
합집합을 나타낼 때 두 집합이 공통적으로 갖고 있는 원소는 중복을 제거하고 하나만 표시된다.
mySQL에서는 8.0 기준으로 UNION 연산자만 지원한다.
INTERSECT: A ∩ B 일 때 사용
MINUS/EXCEPT: A - B, B - A 일 때 사용
UNION: A U B일 때 사용

같은 종류의 테이블을 조인하기

특정 테이블에 있는 값만 확인하고 싶은 경우

SELECT
    old.id AS old_id,
    old.name AS old_name,
    new.id AS new_id,
    new.name AS new_name
FROM item AS old LEFT OUTER JOIN item_mew AS new
ON old.id = new.id;

신상중 null값만 확인하고 싶은 경우

SELECT
    old.id AS old_id,
    old.name AS old_name,
    new.id AS new_id,
    new.name AS new_name
FROM item AS old LEFT OUTER JOIN item_mew AS new
ON old.id = new.id
WHERE old.id IS NULL;

둘다 같이 있는 종류를 보고 싶은 경우

SELECT
    old.id AS old_id,
    old.name AS old_name,
    new.id AS new_id,
    new.name AS new_name
FROM item AS old INNER JOIN item_mew AS new
ON old.id = new.id;

둘다 합쳐보고 싶은 경우

1
2
3

SELECT * FROM item
UNION
SELECT * FROM item_new;

ON 대신 USING 사용

join의 조건을 설정할 때 ON 절을 사용했지만 만약 조인 조건으로 쓰인 두 컬럼의 이름이 같으면 ON 대신 USING을 쓰는 경우도 있다.
두 테이블에서 조인 조건으로 사용되는 컬럼들의 이름이 같으면 그냥 USING이라고 쓰고 그 안에 컬럼 이름을 쓰는 것도 허용
ON old.id = new.id와 USING(id) 는 같다.

UNION

서로 다른 종류의 테이블도, 조회하는 컬럼을 일치시키면 집합 연산이 가능하다.
테이블의 원래 컬럼 구조가 달랃고 두 테이블이 공통적으로 갖고 있는 컬럼들만 조회한 경우 UNION같은 집합 연산을 수행할 수 있다.
총 컬럼수와 컬럼 데이터 타입만 일치하면 UNION 연산이 가능하다.
교집합에 해당하는 영역의 row들은 중복을 제거하고 하나의 row만 보여준다.
UNION ALL: 중복 제거 작업 없이 두 테이블을 합친 결과를 그대로 보여준다.

서로 다른 3개의 테이블 조인하기

SELECT
    i.name, i.id,
    r.item_id, r.star, r.comment, r.mem_id,
    m.id, m.email
FROM
    item AS i LEFT OUTER JOIN review AS r
        ON r.item_id = i.id
    LEFT OUTER JOIN member AS m
        ON r.mem_id = m.id;

의미있는 데이터 추출하기

각 상품별로 여성 회원들이 남긴 별점들의 평균값

SELECT i.id, i.name, AVG(star), COUNT(*)
FROM
    item AS i LEFT OUTER JOIN review AS r
        ON r.item_id = i.id
    LEFT OUTER JOIN member AS m
        ON r.mem_id = m.id
    WHERE m.gender = 'f'
    GROUP BY i.id, i.name
    HAVING COUNT(*) > 1
    ORDER BY AVG(star) DESC,
    COUNT(*) DESC;

리뷰 내용 확인하기

1	SELECT * FROM review WHERE item_id = 2;

그 외의 조인들

NATURAL JOIN (자연조인)

두 테이블에서 같은 이름의 컬럼을 찾아서 자동으로 그것들을 조인 조건을 설정하고, INNER JOIN을 해주는 조인
조인 조건을 자동으로 설정해주기 때문에 ON절을 쓸 필요가 없다.

CROSS JOIN

한 테이블의 하나의 row에 다른 테이블의 모든 row들을 매칭하고, 그 다음 row에서도 또, 다른 테이블의 모든 row들을 매칭하는 것을 반복함으로써 두 테이블의 row들의 모든 조합을 보여주는 조인
두 테이블의 Cartesian Product를 구하는 조인
카르테시안 곱(Cartesian Product): 두 집합의 모든 원소들의 조합을 나타내는 것을 수학의 집합 이론

SELF JOIN

테이블이 자기 자신과 조인을 하는 경우

FULL OUTER JOIN

두 테이블의 LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN 결과를 합치는 조인
두 결과에 모두 존재하는 row들(두 테이블에 공통으로 존재하던 row들)은 한번만 표현

Non-Equi 조인

ON절에서 등호(=)가 없는 조건 표현식

REFERENCE
코드잇 온라인 강의 개발자를 위한 SQL 데이터베이스