2021-12-03 13:20 작성
My SQL basic 메모
Table of contents
Join
관계형 데이터베이스는 다수의 관계된 표들이 foreign key라고 알려진 일반적 열들을 이용하여 서로 연결돼 구성되어 있다. 그렇기 때문에 각각의 표 안의 데이터들은 비즈니스 측면에서 불완전해보이게 된다.
예를 들어 아래의 샘플 데이터베이스에서 orders와 orderdetails 표는 orderNumber 열을 통해 서로 연결되어 있다.
완전한 order의 정보를 얻기 위해서는 orders와 orderdetails로부터 데이터를 쿼리할 필요가 있다.
여기에서 joins의 역할이 대두된다.
Join은 하나(self-join) 혹은 그 이상의 표 사이에서 일반적 열의 값에 기초해 데이터를 서로 연결하는 method를 말한다.
MySQL은 아래의 4가지 유형의 joins를 지원한다.
- Inner join
- Left join
- Right join
- Cross join
표를 join 하는데 있어 cross join, inner join, left join 혹은 right join을 사용할 수 있다. Join은 FROM 이후에 나타나 SELECT 구문 안에서 쓰인다.
참고로, MySQL은 아직
FULL OUTER JOIN을 지원하지 않는다.
1. 샘플 표 세팅하기
먼저, members와 committes라고 불리는 두개의 표를 생성한다:
CREATE TABLE members (
member_id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (member_id)
);
CREATE TABLE committees (
committee_id INT AUTO_INCREMENT,
name VARCHAR(100),
PRIMARY KEY (committee_id)
);
둘째, 몇 개의 rows를 members와 committees tables에 넣는다:
INSERT INTO members(name)
VALUES('John'),('Jane'),('Mary'),('David'),('Amelia');
INSERT INTO committees(name)
VALUES('John'),('Mary'),('Amelia'),('Joe');
셋째, members와 committees의 데이터를 쿼리한다.
SELECT * FROM members;
output:
| member_id | name |
|---|---|
| 1 | John |
| 2 | Jane |
| 3 | Mary |
| 4 | David |
| 5 | Amelia |
| committee_id | name |
|---|---|
| 1 | John |
| 2 | Mary |
| 3 | Amelia |
| 4 | Joe |
몇몇 멤버들은 committee의 멤버이기도 하지만 그렇지 않은 멤버도 있다. committee 멤버들도 마찬가지다.
2. MySQL INNER JOIN
아래는 INNER JOIN의 기초 문법에 관해 보여준다.
SELECT column_list
FROM table_1
INNER JOIN table_2 ON join_condition;
INNER JOIN은 join의 서술부에 쓰인 조건에 기초해 두 개의 tables를 join한다.
INNER JOIN은 첫 번째 table의 각각의 row를 두 번째 table의 모든 row와 비교한다. 그리고 양 tables로부터 매칭되는 rows를 포함한다.
만약 join 조건이 동등 연산자(=)를 사용하고 매칭에 사용된 양 tables의 column names가 똑같다면, USING을 대체재로 사용할 수 있다.
아래의 구문은 INNER JOIN을 사용해 members이면서 동시에 committee members를 찾는다.
-- members가 기준이 되고 committees는 비교대상이 됨
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
INNER JOIN committees c ON c.name = m.name;
output:
| member_id | member | committee_id | committee |
|---|---|---|---|
| 1 | John | 1 | John |
| 3 | Mary | 2 | Mary |
| 5 | Amelia | 3 | Amelia |
이 예시에서 INNER JOIN은 양 members와 committees tables의 name 값들을 매치시키는데 사용한다.
다음의 벤 다이어그램은 INNER JOIN을 시각적으로 보여준다.
양 tables는 같은 column을 매치시키는데 사용하고 있기 때문에 USING을 사용할 수 있다.
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
INNER JOIN committees c USING(name);
3. MySQL LEFT JOIN
INNER JOIN과 비슷하게 LEFT JOIN도 JOIN 서술부가 필요하다.
LEFT JOIN은 왼쪽 table부터 데이터를 선택한다. 왼쪽 table의 각각의 row를 오른쪽 table의 모든 row와 비교한다.
LEFT JOIN은 오른쪽 table의 rows와 매치되는지 여부와 상관없이 왼쪽 table의 모든 데이터를 선택한다.
오른쪽 table과의 매칭이 없는 경우에 LEFT JOIN은 오른쪽 table의 특정 row의 columns에 NULLs를 사용한다.
아래는 기본적 문법 형태이다:
SELECT column_list
FROM table_1
LEFT JOIN table_2 ON join_condition;
LEFT JOIN은 양 tables에 사용된 column의 명칭이 같을 경우를 대비해 USING을 지원한다.
SELECT column_list
FROM table_1
LEFT JOIN table_2 USING (column_name);
member와 committees를 이용한 예시를 보자:
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
LEFT JOIN committees c USING(name);
output: 왼쪽 table의 모든 값이 출력되고 오른쪽 table의 매치되지 않는 부분은 NULL 처리 된 것을 알 수 있다.
| member_id | member | committee_id | committee |
|---|---|---|---|
| 1 | John | 1 | John |
| 2 | Jane | NULL | NULL |
| 3 | Mary | 2 | Mary |
| 4 | David | NULL | NULL |
| 5 | Amelia | 3 | Amelia |
벤다이어그램을 사용하면 다음과 같다.
USING 문법을 사용하면 다음과 같다.
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
LEFT JOIN committees c USING(name);
committee members에 속하지 않는 members를 찾기 위해 WHERE 절에 IS NULL 연산자를 사용할 수 있다.
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
LEFT JOIN committees c USING(name)
WHERE c.committee_id IS NULL;
output:
| member_id | member | committee_id | committee |
|---|---|---|---|
| 2 | Jane | NULL | NULL |
| 4 | David | NULL | NULL |
일반적으로, 이 쿼리 패턴은 오른쪽 table의 rows와 일치하지 않는 rows를 찾는 용도로 사용할 수 있다.
4. MySQL RIGHT JOIN
RIGHT JOIN은 LEFT JOIN과 유사하며 그 역할이 반대일 뿐이다.
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
RIGHT JOIN committees c on c.name = m.name;
output:
| member_id | member | committee_id | committee |
|---|---|---|---|
| 1 | John | 1 | John |
| 3 | Mary | 2 | Mary |
| 5 | Amelia | 3 | Amelia |
| NULL | NULL | 4 | Joe |
5. MySQL CROSS JOIN
INNER JOIN, LEFT JOIN 그리고 RIGHT JOIN과 달리 CROSS JOIN은 join 조건이 존재하지 않는다.
CROSS JOIN은 첫 table의 각각의 row를 두 번째 table의 모든 row에 포함시킨다.
첫 테이블이 n rows를 가지고 있고 두 번째 테이블이 m rows를 가지고 있다고 가정하자. CROSS JOIN은 n * m rows를 반환할 것이다.
SELECT select_list
FROM table_1
CROSS JOIN table_2;
SELECT
m.member_id,
m.name AS member,
c.committee_id,
c.name AS committee
FROM
members m
CROSS JOIN committees c;
output: 5 * 4 = 총 20 개를 반환한다.
| member_id | member | committee_id | committee |
|---|---|---|---|
| 1 | John | 4 | Joe |
| 1 | John | 3 | Amelia |
| 1 | John | 2 | Mary |
| 1 | John | 1 | John |
| 2 | Jane | 4 | Joe |
| 2 | Jane | 3 | Amelia |
| 2 | Jane | 2 | Mary |
| 2 | Jane | 1 | John |
| 3 | Mary | 4 | Joe |
| 3 | Mary | 3 | Amelia |
| 3 | Mary | 2 | Mary |
| 3 | Mary | 1 | John |
| 4 | David | 4 | Joe |
| 4 | David | 3 | Amelia |
| 4 | David | 2 | Mary |
| 4 | David | 1 | John |
| 5 | Amelia | 4 | Joe |
| 5 | Amelia | 3 | Amelia |
| 5 | Amelia | 2 | Mary |
| 5 | Amelia | 1 | John |
CROSS JOIN은 계획의 목적으로 하는 데이터를 발생시키는데 유용하다. 예를 들어, 고객, 제품, 연도 정보를 CROSS JOIN로 통합해 판매 계획을 짤 수 있다.