[SQL][DB] JOIN
개요
DB 설계 시 대부분은 하나의 테이블에 모든 데이터를 넣지 않고 여러 테이블로 분산하여 넣는다고 한다. 이렇게 설계하면 두 테이블로부터 흩어져 있는 데이터를 취합하여 가져올 때도 있을 것이다. 이럴 때 사용하는 것이 join이다. join은 방금 말했듯, 둘 이상의 테이블로부터 데이터들을 가져와 취합하는 기술이다.
JOIN의 종류
join에도 여러 종류가 있다.
- Cartesian Product Join(데카르트 곱 조인, Cross Join, 상호 조인이라고도 함) : 해당 조인은 한 테이블 내 모든 행과 다른 테이블 내 모든 행을 모두 조인하는 방식이다. 예를 들어 a라는 테이블의 행의 개수가 a개, b라는 테이블의 행의 개수가 b개라면, 총 ab개의 데이터들을 가져오는 방식이다. a 테이블의 한 행이 b 테이블의 모든 행과 한 번씩 조인하여 가져오고, 그 다음에 a 테이블의 다른 한 행이 같은 방식을 거치는 형식이다. 모든 경우의 수로 짝을 짓는 것이다. 이러한 방식은 별로 연관이 없는 데이터들끼리도 합쳐서 가져올 수 있기에 불필요하고, 모든 데이터를 다 조인시켜 가져오기에 성능적으로도 좋을리가 없다. 이 조인 방식은 보통 학술적으로, 또는 테스트를 위해 무작위의 대용량 데이터 샘플이 필요하는 등의 제한적인 기능으로만 쓰이지, 실무에선 거의 쓰이지 않는다고 한다. SQL에서는 다음과 같은 문법 구조를 이용하면 된다고 한다.
SELECT * FROM table1 CROSS JOIN table2;
Cross join 문법 구조.
- Equi Join(등가 조인) : 두 테이블에서 공통 필드를 가지고 있을 때, 이를 이용하여 레코드를 가져오는 방식의 조인이다. 예를 들어 a 테이블에 “사원이름”이란 필드가 있고, 이 똑같은 필드가 b 테이블에도 있으면 이 공통 필드를 이용하여 데이터를 가져오는 방식이다. 그냥 “등가 조인”이라 하면 중복되는 데이터들도 가져온다.
한 편 등가 조인에도 여러 세부 종류가 있다.
- Inner Join(내부 조인): Equi join과 동일한 방식으로 진행되나, 중복되는 데이터는 가져오지 않는 방식. 실무에서 대부분 사용하는 방식이라고 한다.
- Outer Join(외부 조인) : Inner Join을 확장한 개념으로, inner join의 결과와 더불어 두 테이블 내 공통되지 않은 데이터까지 가져오는 방식이다. 이 공통되지 않은 데이터를 두 테이블 중 어느 테이블에서 가져오냐에 따라 Left outer join, Right outer join, full outer join이 존재한다. left는 말 그대로 왼쪽 테이블에서 가져오는 것을, full은 양쪽 테이블 모두에서 가져온다는 뜻이다.
- Non Equi Join (비등가 조인) : 두 테이블 간 공통 필드가 없을 때 사용하는 방식으로, ON 조건문에서 범위를 특정하여 데이터를 가져오는 방식이다.
- Self Join (자체 조인): 한 테이블에서 자기 자신을 조인하는 방법. 그래서 단 하나의 테이블만을 가지고 사용한다. 하나의 테이블에서 그 테이블을 복제한 똑같은 테이블을 조인하여 사용한다고 보면 되겠다. 문법은 inner join과 동일하게 사용하되, 하나의 테이블에 두 개의 서로 다른 별칭을 부여하여 마치 서로 다른 두 테이블을 조인하듯이 사용한다.
그렇다면 SQL에서 join 문법을 살펴보겠다.
Inner Join
내부 조인의 형식은 다음과 같다.
-- 표준 SQL
SELECT <열 목록>
FROM <첫 번째 테이블>
INNER JOIN <두 번째 테이블> -- JOIN이라고만 써도 INNER JOIN으로 인식한다.
ON <조인될 조건>
[WHERE 검색 조건]
-- 비표준 문법
SELECT <열 목록>
FROM <첫 번째 테이블>
INNER JOIN <두 번째 테이블>
WHERE <조인될 조건>
여기서는 표준 SQL 문법을 사용할 것이다.
join을 살펴보기 위해 다음과 같이 테이블을 생성하고 데이터를 삽입하겠다.
CREATE TABLE classes (
class_number int,
class_name varchar(20),
bonus int -- 향후 지급할 보너스 마일리지
);
INSERT INTO classes VALUES (1, 'vvip', 5000),
(2, 'vip', 4000),
(3, 'gold', 3000),
(4, 'silver', 2000),
(5, 'bronze', 1000);
/*
MariaDB [mytestmaria]> select * from classes
-> ;
+--------------+------------+-------+
| class_number | class_name | bonus |
+--------------+------------+-------+
| 1 | vvip | 5000 |
| 2 | vip | 4000 |
| 3 | gold | 3000 |
| 4 | silver | 2000 |
| 5 | bronze | 1000 |
+--------------+------------+-------+
5 rows in set (0.001 sec)
*/
예제 1-1
이제 inner join을 이용하여 두 테이블의 내용들을 하나로 합쳐 가져와 보겠다. 다음은 모든 회원의 유저이름, 마일리지, 클래스 넘버와 클래스 이름, 클래스별 받게될 보너스 마일리지를 가져오는 쿼리문이다.
SELECT username, mileage, su.class_number, class_name, bonus
FROM site_users su INNER JOIN classes cl
ON su.class_number = cl.class_number;
예제 1-2
+-----------------+---------+--------------+------------+-------+
| username | mileage | class_number | class_name | bonus |
+-----------------+---------+--------------+------------+-------+
| pcopozio0 | 1549 | 1 | vvip | 5000 |
| eszanto1 | 2712 | 4 | silver | 2000 |
| jmatuszkiewicz2 | 1129 | 5 | bronze | 1000 |
| egudgion3 | 4238 | 2 | vip | 4000 |
| gfittes4 | 4632 | 3 | gold | 3000 |
| dbuttrum5 | 2235 | 4 | silver | 2000 |
| lfree6 | 1891 | 5 | bronze | 1000 |
| bleggis7 | 1396 | 3 | gold | 3000 |
| kmardle8 | 4058 | 2 | vip | 4000 |
| dsummerside9 | 4042 | 5 | bronze | 1000 |
| fwillimonta | 1282 | 2 | vip | 4000 |
| atailourb | 1546 | 1 | vvip | 5000 |
| dmulgrewc | 3492 | 2 | vip | 4000 |
| ksimeolid | 4908 | 4 | silver | 2000 |
| lscaddinge | 4640 | 1 | vvip | 5000 |
| srenonf | 3486 | 5 | bronze | 1000 |
| aledesg | 2954 | 4 | silver | 2000 |
| hshickleh | 2632 | 1 | vvip | 5000 |
| lglandersi | 3712 | 5 | bronze | 1000 |
| dionsj | 2596 | 1 | vvip | 5000 |
+-----------------+---------+--------------+------------+-------+
예제 1-2 실행결과
위 예제의 실행결과를 보면 두 테이블로부터 데이터를 가져온 것을 볼 수 있다. class_name과 bonus는 classes라는 테이블에 존재하고, 나머지 필드들은 site_users 테이블에 존재하는데 두 테이블의 공통 필드인 class_number를 이용하여 하나로 합쳐 가져온 것을 확인할 수 있다.
한 가지 주의할 것이 있다. SELECT 뒤에 나올 필드 중 두 테이블에 공통으로 나오는 필드가 있으면, 이 필드가 어느 테이블에 오는 것인지 명확히 표시해줘야 한다. 위 예제의 경우, su.class_number라 명시함으로써 site_users의 class_number 필드를 가져오겠다고 명시해주었다. 이렇게 하지 않을 경우, 프로그램에서는 두 테이블 모두 같은 필드명을 가지므로 둘 중 어디서 가져올지 몰라 에러를 내게 된다.
SELECT username, mileage, class_number, class_name, bonus
FROM site_users su INNER JOIN classes cl
ON su.class_number = cl.class_number;
-- ERROR 1052 (23000): Column 'class_number' in field list is ambiguous
예제 1-3
join으로 데이터를 가져올 때, 조건을 추가해 조건에 부합하는 데이터만 가져올 수 있다. 이 때는 두 가지 방법이 있다. 하나는 on 구문에 and 연산자로 연결하여 한꺼번에 작성하는 방식이고, 또 하나는 WHERE절에 따로 작성하는 방법이다.
-- #1
SELECT username, mileage, su.class_number, class_name, bonus
FROM site_users su INNER JOIN classes cl
ON su.class_number = cl.class_number
AND bonus > 3000;
-- #2
SELECT username, mileage, su.class_number, class_name, bonus
FROM site_users su INNER JOIN classes cl
ON su.class_number = cl.class_number
WHERE bonus > 3000;
예제 1-4
+-------------+---------+--------------+------------+-------+
| username | mileage | class_number | class_name | bonus |
+-------------+---------+--------------+------------+-------+
| pcopozio0 | 1549 | 1 | vvip | 5000 |
| egudgion3 | 4238 | 2 | vip | 4000 |
| kmardle8 | 4058 | 2 | vip | 4000 |
| fwillimonta | 1282 | 2 | vip | 4000 |
| atailourb | 1546 | 1 | vvip | 5000 |
| dmulgrewc | 3492 | 2 | vip | 4000 |
| lscaddinge | 4640 | 1 | vvip | 5000 |
| hshickleh | 2632 | 1 | vvip | 5000 |
| dionsj | 2596 | 1 | vvip | 5000 |
| good123 | 3000 | 1 | vvip | 5000 |
+-------------+---------+--------------+------------+-------+
10 rows in set (0.002 sec)
예제 1-4 실행결과
두 쿼리 모두 결과는 같다.
그런데 한 가지 차이점이라면, ON 조건에 모든 조건문을 사용하는 경우, JOIN이 되기 전에 미리 필터링하여 데이터를 가져오는 반면, WHERE절로 따로 조건문을 사용하는 경우, JOIN을 한 후에 필터링한다는 차이점이 있다.
ON 조건문은 주로 내부 조인을 위해 사용하는 것이고, WHERE절은 일반적으로 가져온 데이터들로부터 특정 조건을 만족하는 데이터들만을 가져오기 위한 목적이므로 되도록 WHERE절에 쓸 수 있는 조건식은 WHERE절에 따로 작성하여 해당 쿼리가 조인을 위한 것인지 단순한 조건 필터링을 위한 것인지 분리하는 게 가독성에도 좋을 것 같다.
또한, ON 조건문에 한꺼번에 작성하는 방식과 WHERE절에 따로 작성하는 방식은 언뜻보면 똑같아 보이나 가끔 서로 다른 결과가 나올 수도 있다고 한다.
Outer Join
외부 조인의 문법 구조는 다음과 같다.
SELECT <열 이름>
FROM <왼쪽 테이블> LEFT OUTER JOIN <오른쪽 테이블> -- #1
ON <조인될 조건>
[WHERE <검색 조건>];
위 예제는 왼쪽 외부 조인으로, 오른쪽 외부 조인을 원하면 RIGHT를 대신 사용하면 된다.
참고로, mariaDB, mySql에서는 full outer join을 지원하지 않는다고 한다. 대신 UNION 키워드를 사용하면 된다. 이에 대해선 나중에 다른 페이지에서 다뤄볼 예정이다.
다음은 회원 정보와 더불어, 각 회원들의 클래스명과 차후 받게될 보너스 마일리지를 출력하는 예제이다. 단, 클래스명과 보너스 마일리지는 모두 출력하게끔 한다.
-- RIGHT OUTER JOIN #1
SELECT *
FROM site_users su RIGHT OUTER JOIN classes cl
ON su.class_number = cl.class_number AND
su.class_number <= 3;
-- 비교를 위해 같은 조건의 내부 조인 쿼리도 추가.
-- #2
SELECT *
FROM site_users su INNER JOIN classes cl
ON su.class_number = cl.class_number AND
su.class_number <= 3;
예제 2-1
#1
+-----------+--------------+--------------+---------+-------------+---------------+--------------+------------+-------+
| member_id | sign_up_date | class_number | mileage | username | aver_purchase | class_number | class_name | bonus |
+-----------+--------------+--------------+---------+-------------+---------------+--------------+------------+-------+
| 1 | 2023-10-23 | 1 | 1549 | pcopozio0 | 616 | 1 | vvip | 5000 |
| 4 | 2024-06-04 | 2 | 4238 | egudgion3 | NULL | 2 | vip | 4000 |
| 5 | 2023-12-06 | 3 | 4632 | gfittes4 | 592 | 3 | gold | 3000 |
| 8 | 2023-07-03 | 3 | 1396 | bleggis7 | NULL | 3 | gold | 3000 |
| 9 | 2024-05-04 | 2 | 4058 | kmardle8 | NULL | 2 | vip | 4000 |
| 11 | 2023-01-08 | 2 | 1282 | fwillimonta | NULL | 2 | vip | 4000 |
| 12 | 2024-02-21 | 1 | 1546 | atailourb | NULL | 1 | vvip | 5000 |
| 13 | 2024-07-09 | 2 | 3492 | dmulgrewc | 807 | 2 | vip | 4000 |
| 15 | 2024-05-16 | 1 | 4640 | lscaddinge | 656 | 1 | vvip | 5000 |
| 18 | 2023-01-06 | 1 | 2632 | hshickleh | NULL | 1 | vvip | 5000 |
| 20 | 2024-03-13 | 1 | 2596 | dionsj | 761 | 1 | vvip | 5000 |
| 21 | 2024-08-07 | 1 | 3000 | good123 | 855 | 1 | vvip | 5000 |
| NULL | NULL | NULL | NULL | NULL | NULL | 4 | silver | 2000 |
| NULL | NULL | NULL | NULL | NULL | NULL | 5 | bronze | 1000 |
+-----------+--------------+--------------+---------+-------------+---------------+--------------+------------+-------+
14 rows in set (0.001 sec)
#2
+-----------+--------------+--------------+---------+-------------+---------------+--------------+------------+-------+
| member_id | sign_up_date | class_number | mileage | username | aver_purchase | class_number | class_name | bonus |
+-----------+--------------+--------------+---------+-------------+---------------+--------------+------------+-------+
| 1 | 2023-10-23 | 1 | 1549 | pcopozio0 | 616 | 1 | vvip | 5000 |
| 4 | 2024-06-04 | 2 | 4238 | egudgion3 | NULL | 2 | vip | 4000 |
| 5 | 2023-12-06 | 3 | 4632 | gfittes4 | 592 | 3 | gold | 3000 |
| 8 | 2023-07-03 | 3 | 1396 | bleggis7 | NULL | 3 | gold | 3000 |
| 9 | 2024-05-04 | 2 | 4058 | kmardle8 | NULL | 2 | vip | 4000 |
| 11 | 2023-01-08 | 2 | 1282 | fwillimonta | NULL | 2 | vip | 4000 |
| 12 | 2024-02-21 | 1 | 1546 | atailourb | NULL | 1 | vvip | 5000 |
| 13 | 2024-07-09 | 2 | 3492 | dmulgrewc | 807 | 2 | vip | 4000 |
| 15 | 2024-05-16 | 1 | 4640 | lscaddinge | 656 | 1 | vvip | 5000 |
| 18 | 2023-01-06 | 1 | 2632 | hshickleh | NULL | 1 | vvip | 5000 |
| 20 | 2024-03-13 | 1 | 2596 | dionsj | 761 | 1 | vvip | 5000 |
| 21 | 2024-08-07 | 1 | 3000 | good123 | 855 | 1 | vvip | 5000 |
+-----------+--------------+--------------+---------+-------------+---------------+--------------+------------+-------+
12 rows in set (0.001 sec)
예제 2-1 실행결과
위 예제의 실행결과에서 외부 조인과 내부 조인의 차이를 알 수 있다. 내부 조인의 경우, 조건에 맞는 데이터들만 가져오는 반면, 외부 조인은 조건에 맞는 데이터와 더불어 나머지 하나의 테이블 내 다른 모든 데이터들까지 가져온다. 그래서 위 예제의 외부 조인의 결과, 조건에 맞지 않는 데이터들까지 검색되었기에 다른 필드들의 값이 NULL로 나온 것이다.
Non Equi Join
Non Equi Join도 특별한 문법을 가지는 것은 아니다. inner join과 똑같은 키워드를 사용하며, 단지 ON 조건문에서 다른 조인들은 주로 등호(=) 기호를 통해 서로 같은 데이터들을 가져온다면, non equi join은 주로 >, < 등을 이용하여 데이터를 범위로 가져오는 것이 차이이다.
실습을 위해 다음의 새로운 테이블을 생성하고 새 데이터들을 삽입하겠다.
CREATE TABLE loyalty (
grade int,
low_avg_pch int,
high_avg_pch int
);
INSERT INTO loyalty VALUES
(1, 900, 1000),
(2, 800, 899),
(3, 700, 799),
(4, 600, 699),
(5, 0, 599);
예제 3-1
위 쿼리는 고객의 충성도를 확인하기 위해, 그에 대한 지표로 평균 구매액을 보고 있다.
이제 site_users 테이블에 있는 회원 정보와 함께, 회원들의 충성도 등급을 같이 보고자 한다. 그런데 두 테이블에는 공통된 필드가 없다. 이럴 때 비등가 조인을 사용할 수 있다.
SELECT class_number, aver_purchase, username, grade
FROM site_users su INNER JOIN loyalty ly
ON su.aver_purchase BETWEEN ly.low_avg_pch
AND ly.high_avg_pch
ORDER BY grade, class_number;
예제 3-2
+--------------+---------------+-----------------+-------+
| class_number | aver_purchase | username | grade |
+--------------+---------------+-----------------+-------+
| 1 | 855 | good123 | 2 |
| 2 | 807 | dmulgrewc | 2 |
| 4 | 850 | aledesg | 2 |
| 5 | 855 | jmatuszkiewicz2 | 2 |
| 1 | 761 | dionsj | 3 |
| 1 | 616 | pcopozio0 | 4 |
| 1 | 656 | lscaddinge | 4 |
| 3 | 592 | gfittes4 | 5 |
| 5 | 322 | srenonf | 5 |
| 5 | 249 | dsummerside9 | 5 |
| 5 | 547 | lfree6 | 5 |
| 5 | 100 | lglandersi | 5 |
+--------------+---------------+-----------------+-------+
12 rows in set (0.001 sec)
예제 3-2 실행결과
이렇게 범위를 지정하는 방법을 통해 두 테이블 간 공통 필드가 없더라도 조인을 할 수 있다.
Self Join
자체 조인도 다른 조인과 마찬가지로 내부 조인이 쓰는 INNER JOIN 키워드를 사용한다. 단지, 자기 자신의 테이블을 마치 이중인격(?)마냥 두개로 분열하여 자기 자신을 조인을 하고, 이를 위해 같은 테이블명에 서로 다른 2개의 별칭을 부여해야 한다는 점이 다르다.
실습을 위해 site_users에 “추천자”라는 필드를 추가하겠다. 이 필드는 같은 테이블 내 다른 회원들 중 이 사이트의 가입을 추천한 한 명의 유저 이름 데이터가 들어갈 자리이다.
-- 새 필드명 추가.
ALTER TABLE site_users
ADD COLUMN recomm_by VARCHAR(50);
-- 해당 필드에 값을 랜덤으로 추가.
UPDATE site_users
SET recomm_by =
(SELECT MIN(username) FROM site_users
WHERE member_id = FLOOR(1 + (RAND() * 21))
);
예제 4-1
MariaDB [sql_practice]> select * from site_users;
+-----------+--------------+--------------+---------+-----------------+---------------+-----------------+
| member_id | sign_up_date | class_number | mileage | username | aver_purchase | recomm_by |
+-----------+--------------+--------------+---------+-----------------+---------------+-----------------+
| 1 | 2023-10-23 | 1 | 1549 | pcopozio0 | 616 | NULL |
| 2 | 2024-01-09 | 4 | 2712 | eszanto1 | NULL | NULL |
| 3 | 2023-08-15 | 5 | 1129 | jmatuszkiewicz2 | 855 | lglandersi |
| 4 | 2024-06-04 | 2 | 4238 | egudgion3 | NULL | dmulgrewc |
| 5 | 2023-12-06 | 3 | 4632 | gfittes4 | 592 | jmatuszkiewicz2 |
| 6 | 2023-09-23 | 4 | 2235 | dbuttrum5 | NULL | egudgion3 |
| 7 | 2024-07-21 | 5 | 1891 | lfree6 | 547 | NULL |
| 8 | 2023-07-03 | 3 | 1396 | bleggis7 | NULL | NULL |
| 9 | 2024-05-04 | 2 | 4058 | kmardle8 | NULL | eszanto1 |
| 10 | 2023-05-06 | 5 | 4042 | dsummerside9 | 249 | NULL |
| 11 | 2023-01-08 | 2 | 1282 | fwillimonta | NULL | lfree6 |
| 12 | 2024-02-21 | 1 | 1546 | atailourb | NULL | eszanto1 |
| 13 | 2024-07-09 | 2 | 3492 | dmulgrewc | 807 | bleggis7 |
| 14 | 2024-06-14 | 4 | 4908 | ksimeolid | NULL | lfree6 |
| 15 | 2024-05-16 | 1 | 4640 | lscaddinge | 656 | NULL |
| 16 | 2023-10-10 | 5 | 3486 | srenonf | 322 | jmatuszkiewicz2 |
| 17 | 2024-03-27 | 4 | 2954 | aledesg | 850 | NULL |
| 18 | 2023-01-06 | 1 | 2632 | hshickleh | NULL | NULL |
| 19 | 2022-09-11 | 5 | 3712 | lglandersi | 100 | good123 |
| 20 | 2024-03-13 | 1 | 2596 | dionsj | 761 | NULL |
| 21 | 2024-08-07 | 1 | 3000 | good123 | 855 | dionsj |
+-----------+--------------+--------------+---------+-----------------+---------------+-----------------+
예제 4-1 실행결과
이제 회원들의 정보와 더불어 해당 회원의 추천인도 자체 조인을 이용하여 검색해보자.
SELECT su1.username, su1.recomm_by
FROM site_users su1 INNER JOIN site_users su2
ON su1.recomm_by = su2.username;
예제 4-2
+-----------------+-----------------+
| username | recomm_by |
+-----------------+-----------------+
| kmardle8 | eszanto1 |
| atailourb | eszanto1 |
| gfittes4 | jmatuszkiewicz2 |
| srenonf | jmatuszkiewicz2 |
| dbuttrum5 | egudgion3 |
| fwillimonta | lfree6 |
| ksimeolid | lfree6 |
| dmulgrewc | bleggis7 |
| egudgion3 | dmulgrewc |
| jmatuszkiewicz2 | lglandersi |
| good123 | dionsj |
| lglandersi | good123 |
+-----------------+-----------------+
12 rows in set (0.011 sec)
예제 4-2 실행결과
회원과 그 회원의 추천인까지 가져올 수 있었다. 그리고 추천인이 없는 회원들은 검색 대상에서도 제외할 수 있음을 확인할수 있다.
3개 이상의 테이블 조인
3개 이상 테이블을 조인할 때의 문법 구조는 다음과 같다.
-- 연속으로 JOIN을 쓴다. #1
SELECT <열 이름>
FROM tblA JOIN tblB
ON <조인할 조건>
JOIN tblC
ON <조인할 조건>
-- 한꺼번에 조인해도 된다. #2
SELECT <열 이름>
FROM tblA
JOIN tblB
JOIN tblC
ON <a, b 테이블 조인 조건>
AND <b, c 테이블 조인 조건>
위 쿼리문의 #1처럼 join - on - join - on 형식으로 마치 사슬처럼 체이닝하여 사용해도 되고, #2처럼 한꺼번에 JOIN문을 몰아 쓰고, 그 뒤에 ON 조건문에 각 테이블끼리 조인할 조건들을 AND조건으로 묶어 사용해도 된다.
References
[1] 에이콘아카데미(강남) 강의
[2] 우재남, “혼자 공부하는 SQL”, (한빛미디어, 2021)
[3] JOIN 에서 WHERE 와 ON 의 차이, 그리고 OUTER JOIN
[4] INNER JOIN에서 ON 절과 WHERE 절 중 어디서 조건을 처리하는 게 효율적일까요?
[5] Non Equi Join
[오라클/SQL] INNER JOIN - NON-EQUI JOIN(비등가 조인)의 개념 및 예제 : 한쪽 테이블이 일종의 등급표와 같이범주를 지정하는 역할을 하는 경우
This content is licensed under
CC BY-NC 4.0
댓글남기기