조인
Foreign Key
Foreign Key(외래키)는 한 테이블의 컬럼 중에서 다른 테이블의 특정 row 하나를 식별할 수 있도록 해주는 컬럼이다.
Foreign Key는 다른 테이블의 특정 row를 식별할 수 있어야 하기에 주로 다른테이블의 Primary Key를 참조한다.
참조를 하는 테이블을 자식테이블이라 하고, 참조를 당하는 테이블을 부모테이블이라 한다.
Foreign Key를 사용하면 자식테이블에 잘못된 정보를 넣을 수 없게된다.
부모 id컬럼 -> 1,2,3,4,5 이면
자식 임의의 컬럼은 1,2,3,4,5이외의 값은 들어갈 수 없다.
JOIN
OUTER JOIN
기준테이블 LEFT OUTER JOIN 테이블
ON 기준테이블.컬럼 = 테이블.컬럼
RIGHT OUTER JOIN은 오른쪽이 기준이된다.
SELECT
item.id,
item.name,
stock.item_id,
stock.inventory_count
FROM item LEFT OUTER JOIN stock
ON item.id = stock.item_id;
item테이블을 기준으로 stock테이블을 합친다. 합치는 기준은 item.id와 stock.item_id의 값을 비교해서 서로 값이 같은 row끼리 가로방향으로 연결하게 된다.
item.id의 값중에서 stock.item_id의 값에는 존재하지 않는 부분은 NULL로 나오게 된다.
| item.id | stock.item_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | null |
| 4 | null |
| 5 | 5 |
Foreign Key를 사용해서 item테이블을 부모테이블로 만들어 놓으면 RIGHT OUTER JOIN으로 기준테이블을 바꿨을때 NULL값이 없을 수 밖에 없다.
| stock.item_id | item.id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 5 | 5 |
alias
조인할 때는 보통 테이블에 alias를 해준다.
SELECT
i.id,
i.name,
s.item_id,
s.inventory_count
FROM item AS i RIGHT OUTER JOIN stock AS s
ON i.id = s.item_id;
작성 순서는 SELECT가 가장 빠르지만, FROM의 실행 순서가 SELECT보다 빠르기 때문에 설정한 alias를 SELECT에서 사용할 수 있다.
컬럼의 alias와 테이블의 alias
컬럼의 alias는 조회 결과에서 설정한 alias로 변환되어 보여지게 하는 용도로 쓰인다.
반면에 테이블의 alias는 조회 결과에서 보기위함이 아니라 sql문의 길이를 줄여 가독성을 높이기 위해 쓰인다. 그리고 조인을 할때 서로 다른 테이블에 같은 이름의 컬럼이 존재하면 sql문에서 그 컬럼을 가리킬 때 무슨 테이블의 컬럼인지를 더 짧게 표현할 수 있다.
테이블에 alias를 사용하면 FROM의 실행순서가 가장 빠르므로 다른 모든 절에서는 설정한 alias로만 나타내야한다. 그렇지 않으면 에러가 나게 된다.
INNER JOIN
INNER JOIN은 기준이 되는 테이블이 따로 없다. 두 테이블간의 교집합을 구한다고 보면된다.
Foreign Key가 설정된 기준으로 조인하면 OUTER JOIN과 INNER JOIN의 결과가 같을 수 밖에 없다.
보통 조인을 할때 Foreign Key를 기준으로 하지만 꼭 그렇지 만은 않다. 서로 같은 의미를 나타내는 컬럼들을 기준으로 조인하기도한다.
USING
조인 조건으로 쓰인 두 컬럼의 이름이 같으면 ON대신 USING을 쓸 수도 있다.
SELECT
i.id,
i.name,
s.item_id,
s.inventory_count
FROM item AS i RIGHT OUTER JOIN stock AS s
ON i.id = s.id;
이렇게 컬럼의 이름이 같다면
SELECT
i.id,
i.name,
s.item_id,
s.inventory_count
FROM item AS i RIGHT OUTER JOIN stock AS s
USING(id);
이렇게 작성할 수도 있다.
여러개 테이블 조인
SELECT
*
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;
item과 review테이블을 LEFT OUTER JOIN하고 조인 기준은 r.item_id와 i.id이다.
그리고 그 결과로 나온 테이블과 member테이블을 LEFT OUTER JOIN하고 조인 기준은 r.mem_id와 m.id이다. 결과적으로 세 테이블이 조인된다.
상품과 리뷰처럼 하나의 상품에 여러개의 리뷰가 작성되는 1:n 관계인 경우에는 조인을 할때 1:n중 1에 해당하는 테이블의 row는 여러번 중복 등장할 수 있다.
결합 연산과 집합 연산
테이블을 합치는 작업을 연산이라고 할때,
결합 연산은 테이블을 가로 방향으로 합치는 것에 관한 연산이고,
집합 연산은 테이블을 세로 방향으로 합치는 것에 관한 연산이다.
조인은 row들을 가로 방향으로 붙이는 작업이므로 결합 연산에 해당한다.
집합 연산
집합 연산은 테이블 하나를 집합 하나로 보고 그 안에 각 row를 하나의 원소로 간주하고 진행되는 연산이다.
집합 연산은 같은 종류의 테이블끼리만 가능하다.
- INTERSECT (교집합)
SELECT * FROM member_A INTERSECT SELECT * FROM member_B - MINUS 또는 EXCEPT (차집합)
SELECT * FROM member_A MINUS SELECT * FROM member_B - UNION (합집합)
SELECT * FROM member_A UNION SELECT * FROM member_BUNION은 교집합에 속하는 원소들은 중복을 제거하고 하나만 표시된다.
mysql에서는 UNION만 지원하고 INTERSECT와 MINUS는 JOIN을 통해 간접적으로 그 결과를 얻어야 한다.
차집합을 구하는 방법은 기존의 테이블(old)과 갱신된 테이블(new)이 있으면 old테이블을 기준으로 조인(OUTER JOIN)하면 old테이블에는 있지만 new테이블에는 없는(NULL) row를 볼 수 있다. 여기서 IS NULL을 사용해 필요한 정보만 추려내면 old테이블을 갱신하며 삭제된 데이터만 확인할 수 있다.
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_new AS new
ON old.id = new.id
WHERE new.id IS NULL;
같은 종류의 테이블이 아니면 집합 연산이 불가능하지만 서로 다른 종류의 테이블도 조회하는 컬럼을 일치시키면 집합연산이 가능해진다.
SELECT id, count FROM member_A
UNION
SELECT id, count FROM member_B
SELECT절 뒤를 두 테이블이 공통적으로 갖고 있는 컬럼으로 바꿔주면 된다.
총 컬럼의 수와, 각 컬럼의 데이터 타입만 일치하면 UNION같은 집합 연산이 가능해진다.
UNION ALL
UNION은 교집합에 속하는 영역의 row들은 중복을 제거하고 표시하게 되는데 UNION ALL을 사용하게 되면 겹치는 것을 중복제거를 하지 않고 그대로 보여주게 된다.
Leave a comment