조인(JOIN)
조인(JOIN)이란 RDBMS에서 하나의 데이터베이스 내 '여러 테이블의 레코드를 조합하여 하나의 열로 표현하는 기법'이다. 즉, 각기 다른 테이블을 한 번에 보여주고자 할 때 쓰는 쿼리라고 할 수 있다.
RDBMS의 가장 큰 장점이자 대표적인 핵심 기능으로서, 실무에서도 매우 많이 사용되는 쿼리이므로 확실하게 이해하고 넘어갈 필요가 있다.
JOIN을 한눈에 이해하기 위한 다이어그램
JOIN을 일단 가장 기본적인 부분으로 구분하면 두 가지로 구분할 수 있다.
등가 조인(EQUI JOIN)
등가 조인(EQUI JOIN)은 Equal(=) 조건을 이용해 JOIN을 하는것을 말한다. 가장 흔히 볼 수 있는 JOIN 방식이기도 하다.
다음 테이블을 통해 예를 들어 보자. 쇼핑몰에서 스피커와 리뷰 데이터가 있다고 하자. 스피커는 '상품' 테이블의 데이터이고, 리뷰는 '상품 리뷰' 테이블에 저장될 것이다.
SELECT * FROM PRODUCT;| PRODUCT_ID | PRODUCT_NAME | PRICE |
|---|---|---|
| 1001 | CreXXXXe 2ch 블루투스 스피커 | 42,500 |
| 1002 | YAXXHA 2.1ch 모니터링 스피커(50W) | 183,000 |
| 1003 | PeXXXle 2ch 블루투스 스피커 | 21,500 |
SELECT * FROM PRODUCT_REVIEW;| REVIEW_ID | PRODUCT_ID | USER_NAME | CONTENT | DATE |
|---|---|---|---|---|
| 01 | 1001 | mino | 크기는 작은데 소리가 진짜 좋네요. | 20230419 |
| 02 | 1001 | yakasaha | 생각보다 가성비 좋은 듯. 잡음도 없어요. | 20230912 |
| 03 | 1002 | kisoek | 확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함 | 20231120 |
SELECT A.PRODUCT_ID,
A.PRODUCT_NAME,
B.USER_NAME,
B.CONTENT,
B.DATE
FROM PRODUCT A,
PRODUCT_REVIEW B
WHERE A.PRODUCT_ID = B.PRODUCT_ID;| PRODUCT_ID | PRODUCT_NAME | USER_NAME | CONTENT | DATE |
|---|---|---|---|---|
| 1001 | CreXXXXe 2ch 블루투스 스피커 | mino | 크기는 작은데 소리가 진짜 좋네요. | 20230419 |
| 1001 | CreXXXXe 2ch 블루투스 스피커 | yakasaha | 생각보다 가성비 좋은 듯. 잡음도 없어요. | 20230912 |
| 1002 | YAXXHA 2.1ch 모니터링 스피커(50W) | kisoek | 확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함 | 20231120 |
PRODUCT 테이블과 PRODUCT_REVIEW 테이블의 각 컬럼이 하나의 테이블로 반환된다. 이때 두 테이블에서 공통되는 컬럼인 PRODUCT_ID를 비교하여 동일한 데이터만 반환하였으며, 따라서 리뷰 데이터가 존재하지 않는 1003번 상품은 반환되지 않았음을 확인할 수 있다.
비등가 조인(Non EQUI JOIN)
비등가 조인(Non EQUI JOIN)은 Equal(=) 조건이 아닌 다른 조건을 사용하지 않는 조인으로, BETWEEN, >, >=, <, <= 등의 조건이 이에 해당한다. 앞서 등가 조인의 상품 리뷰들 중 일정 기간 내에 작성된 리뷰 작성자에게 상품을 주는 이벤트를 진행한다고 하면, 상품 리뷰 테이블과 이벤트 테이블이 JOIN되어야 할 것이다.
SELECT * FROM PRODUCT_REVIEW;| REVIEW_ID | PRODUCT_ID | USER_NAME | CONTENT | DATE |
|---|---|---|---|---|
| 01 | 1001 | mino | 크기는 작은데 소리가 진짜 좋네요. | 20230419 |
| 02 | 1001 | yakasaha | 생각보다 가성비 좋은 듯. 잡음도 없어요. | 20230912 |
| 03 | 1002 | kisoek | 확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함 | 20231120 |
SELECT * FROM EVENT;| EVENT_ID | EVENT_NAME | START_DATE | END_DATE |
|---|---|---|---|
| 101 | 마일리지 500원 증정 | 20230301 | 20230331 |
| 102 | 배송비 무료 쿠폰 증정 | 20230901 | 20231130 |
| 103 | 크리스마스 특별 이벤트 | 20231201 | 20231231 |
SELECT A.EVENT_NAME,
B.USER_NAME,
B.CONTENT,
B.DATE
FROM EVENT A,
PRODUCT_REVIEW B
WHERE B.DATE BETWEEN A.START_DATE AND A.END_DATE;| EVENT_NAME | USER_NAME | CONTENT | DATE |
|---|---|---|---|
| 마일리지 500원 증정 | mino | 크기는 작은데 소리가 진짜 좋네요. | 20230419 |
| 배송비 무료 쿠폰 증정 | yakasaha | 생각보다 가성비 좋은 듯. 잡음도 없어요. | 20230912 |
| 배송비 무료 쿠폰 증정 | kisoek | 확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함 | 20231120 |
DBMS에는 Oracle, MSSQL, MySQL, MaiaDB, PostgreSQL 등 다양한 벤더가 존재한다. 그런데 벤더마다 SQL 문법에 차이가 너무 클 경우 작업자 입장에서 효율성에 문제가 있어 표준이 되는 ANSI SQL을 지정하게 되었다. JOIN 역시 ANSI SQL에 따르는 문법이 있으며, 이를 ANSI JOIN 혹은 표준 조인, Standard JOIN 등으로 부른다.
다음에서 설명할 내부 조인, 외부 조인, 자연 조인, 교차 조인 등은 모두 ANSI JOIN 문법에 해당하는 내용이다.
내부 조인(INNER JOIN)
내부 조인(INNER JOIN)은 가장 많이 사용되는 조인 쿼리로 JOIN 조건을 충족하는 데이터만을 출력하는 방식이다.
앞서 살펴본 등가 조인의 문법과 다른 점은 ON 절을 사용한다는 것이다.
SELECT A.PRODUCT_ID,
A.PRODUCT_NAME,
B.USER_NAME,
B.CONTENT,
B.DATE
FROM PRODUCT A INNER JOIN PRODUCT_REVIEW B
ON A.PRODUCT_ID = B.PRODUCT_ID;| PRODUCT_ID | PRODUCT_NAME | USER_NAME | CONTENT | DATE |
|---|---|---|---|---|
| 1001 | CreXXXXe 2ch 블루투스 스피커 | mino | 크기는 작은데 소리가 진짜 좋네요. | 20230419 |
| 1001 | CreXXXXe 2ch 블루투스 스피커 | yakasaha | 생각보다 가성비 좋은 듯. 잡음도 없어요. | 20230912 |
| 1002 | YAXXHA 2.1ch 모니터링 스피커(50W) | kisoek | 확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함 | 20231120 |
참고로 위의 등가 조인과 같이 'INNER JOIN ... ON ...' 구문을 사용하지 않는 경우를 '암묵적 표현법(implicit notation)'이라고 하고, 'INNER JOIN ... ON ...' 구문을 사용하는 경우를 '명시적 표현법(explicit notation)'이라고 하기도 한다.
외부 조인(OUTER JOIN)
외부 조인(OUTER JOIN)은 JOIN 조건에 충족하는 데이터가 아니어도 출력하는 방식이다. 크게 세 가지로 구분 가능하며, 각각 'JOIN 조건을 충족하지 않지만 출력되는 데이터'를 판별하는 기준점이 된다.
LEFT OUTER JOIN
SQL에서 왼쪽에 표기된 테이블(A)의 모든 데이터와 JOIN 조건을 충족하는 오른쪽 테이블(B)의 데이터가 출력되는 JOIN이다. 이때 오른쪽 테이블에서 JOIN되는 데이터가 없는 행의 경우 값이 NULL로 출력된다.
SELECT * FROM PRODUCT;| PRODUCT_ID | PRODUCT_NAME | PRICE |
|---|---|---|
| 1001 | CreXXXXe 2ch 블루투스 스피커 | 42,500 |
| 1002 | YAXXHA 2.1ch 모니터링 스피커(50W) | 183,000 |
| 1003 | PeXXXle 2ch 블루투스 스피커 | 21,500 |
SELECT * FROM PRODUCT_REVIEW;| REVIEW_ID | PRODUCT_ID | USER_NAME | CONTENT | DATE |
|---|---|---|---|---|
| 01 | 1001 | mino | 크기는 작은데 소리가 진짜 좋네요. | 20230419 |
| 02 | 1001 | yakasaha | 생각보다 가성비 좋은 듯. 잡음도 없어요. | 20230912 |
| 03 | 1002 | kisoek | 확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함 | 20231120 |
SELECT A.PRODUCT_ID,
A.PRODUCT_NAME,
B.USER_NAME,
B.CONTENT,
B.DATE
FROM PRODUCT A LEFT OUTER JOIN PRODUCT_REVIEW B
ON A.PRODUCT_ID = B.PRODUCT_ID;| PRODUCT_ID | PRODUCT_NAME | USER_NAME | CONTENT | DATE |
|---|---|---|---|---|
| 1001 | CreXXXXe 2ch 블루투스 스피커 | mino | 크기는 작은데 소리가 진짜 좋네요. | 20230419 |
| 1001 | CreXXXXe 2ch 블루투스 스피커 | yakasaha | 생각보다 가성비 좋은 듯. 잡음도 없어요. | 20230912 |
| 1002 | YAXXHA 2.1ch 모니터링 스피커(50W) | kisoek | 확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함 | 20231120 |
| 1003 | PeXXXle 2ch 블루투스 스피커 | [NULL] | [NULL] | [NULL] |
RIGHT OUTER JOIN
LEFT OUTER JOIN과 반대로 SQL에서 오른쪽에 표기된 테이블(B)의 데이터가 모두 출력되는 방식이다. 마찬가지로 왼쪽 테이블(A)의 데이터 중 JOIN 조건에 매치되지 않는 행은 값이 NULL로 출력된다.
SELECT * FROM PRODUCT_REVIEW;| REVIEW_ID | PRODUCT_ID | USER_NAME | CONTENT | DATE |
|---|---|---|---|---|
| 01 | 1001 | mino | 크기는 작은데 소리가 진짜 좋네요. | 20230419 |
| 02 | 1001 | yakasaha | 생각보다 가성비 좋은 듯. 잡음도 없어요. | 20230912 |
| 03 | 1002 | kisoek | 확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함 | 20231120 |
SELECT * FROM PRODUCT;| PRODUCT_ID | PRODUCT_NAME | PRICE |
|---|---|---|
| 1001 | CreXXXXe 2ch 블루투스 스피커 | 42,500 |
| 1002 | YAXXHA 2.1ch 모니터링 스피커(50W) | 183,000 |
| 1003 | PeXXXle 2ch 블루투스 스피커 | 21,500 |
SELECT A.USER_NAME,
A.CONTENT,
A.DATE,
B.PRODUCT_ID,
B.PRODUCT_NAME
FROM PRODUCT_REVIEW A RIGHT OUTER JOIN PRODUCT B
ON A.PRODUCT_ID = B.PRODUCT_ID;| USER_NAME | CONTENT | DATE | PRODUCT_ID | PRODUCT_NAME |
|---|---|---|---|---|
| mino | 크기는 작은데 소리가 진짜 좋네요. | 20230419 | 1001 | CreXXXXe 2ch 블루투스 스피커 |
| yakasaha | 생각보다 가성비 좋은 듯. 잡음도 없어요. | 20230912 | 1001 | CreXXXXe 2ch 블루투스 스피커 |
| kisoek | 확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함 | 20231120 | 1002 | YAXXHA 2.1ch 모니터링 스피커(50W) |
| [NULL] | [NULL] | [NULL] | 1003 | PeXXXle 2ch 블루투스 스피커 |
FULL OUTER JOIN
조인되는 테이블의 모든 데이터가 출력되는 방식으로, LEFT OUTER JOIN과 RIGHT OUTER JOIN의 합집합 개념으로 이해할 수 있을 것이다. 단, 이때 중복값은 제거된다.
SELECT * FROM PLAYSTATION_5;| NAME | GENRE |
|---|---|
| Spider-man 2 | Action |
| Baldur's Gate 3 | RPG |
| Ghost of tsushima | Action |
| Sniper Elite 5 | TPS |
SELECT * FROM X_BOX;| NAME | GENRE |
|---|---|
| STARFIELD | RPG |
| Baldur's Gate 3 | RPG |
| Halo infinite | FPS |
| Sniper Elite 5 | TPS |
SELECT A.NAME as P_GAME,
B.NAME as X_GAME,
FROM PLAYSTAION_5 A FULL OUTER JOIN X_BOX B
ON A.NAME = B.NAME;| P_GAME | X_GAME |
|---|---|
| [NULL] | STARFIELD |
| [NULL] | Halo infinite |
| Spider-man 2 | [NULL] |
| Baldur's Gate 3 | Baldur's Gate 3 |
| Ghost of tsushima | [NULL] |
| Sniper Elite 5 | Sniper Elite 5 |
자연 조인(NATURAL JOIN)
A 테이블과 B 테이블에서 동일한 이름을 가진 컬럼들이 모두 동일한 데이터를 가지고 있을 경우 JOIN이 되는 방식이다.
※ MSSQL에서는 지원하지 않는다.
SELECT *
FROM PLAYSTAION_5 A NATURAL JOIN X_BOX B;| NAME | GENRE |
|---|---|
| Baldur's Gate 3 | RPG |
| Sniper Elite 5 | TPS |
만약 PLAYSTATION_5 테이블에서만 'Sniper Elite 5'의 장르를 'Shooting'으로 바꾼다면 어떻게 될까?
SELECT * FROM PLAYSTATION_5;| NAME | GENRE |
|---|---|
| Spider-man 2 | Action |
| Baldur's Gate 3 | RPG |
| Ghost of tsushima | Action |
| Sniper Elite 5 | Shooting |
SELECT *
FROM PLAYSTAION_5 A NATURAL JOIN X_BOX B;| NAME | GENRE |
|---|---|
| Baldur's Gate 3 | RPG |
두 테이블에 존재하는 'Sniper Elite 5' 행에서 GENRE 컬럼의 데이터가 다르므로 NATURAL JOIN 시에는 출력되지 않는다.
교차 조인(CROSS JOIN)
교차 조인(CROSS JOIN)은 별도의 JOIN 조건이 없는 경우 가능한 모든 경우를 출력하는 방식이다. 곱집합(Cartesian product)이라고도 한다.
SELECT emp_name, emp_id FROM EMPLOYEE;| emp_name | emp_id |
|---|---|
| Elly | 001 |
| Sian | 002 |
| Nalo | 003 |
SELECT drink_name, drink_id FROM DRINK;| drink_name | drink_id |
|---|---|
| Coke | A01 |
| Cider | A02 |
| Green tea | B01 |
SELECT A.emp_name,
A.emp_id
B.drink_name
B.drink_id
FROM EMPLOYEE A CROSS JOIN DRINK B;| emp_name | emp_id | drink_name | drink_id |
|---|---|---|---|
| Elly | 001 | Coke | A01 |
| Elly | 001 | Cider | A02 |
| Elly | 001 | Green tea | B01 |
| Sian | 002 | Coke | A01 |
| Sian | 002 | Cider | A02 |
| Sian | 002 | Green tea | B01 |
| Nalo | 003 | Coke | A01 |
| Nalo | 003 | Cider | A02 |
| Nalo | 003 | Green tea | B01 |
셀프 조인(SELF JOIN)
셀프 조인(SELF JOIN)은 말 그대로 자기 자신과 조인하는 방법이다. 하나의 테이블을 2개처럼 취급하여 JOIN하는 것으로, FROM 절에 같은 테이블이 2번 이상 등장하므로 ALIAS를 반드시 표기해야 한다.
다음과 같은 테이블 employees가 있다고 하자.
| emp_no | first_name | last_name | dept_no |
|---|---|---|---|
| 1001 | Bear | Nalo | d001 |
| 1002 | Evy | Frye | d002 |
| 1003 | Yuna | Kim | d002 |
| 1004 | Alis | Key | d003 |
여기서 같은 부서에서 일하는 직원만을 찾고자 할 때 셀프 조인을 사용할 수 있다.
SELECT A.first_name, AS EmployeeName1,
B.first_name AS EmployeeName2,
A.dept_no
FROM employees as A, employees as B
WHERE A.emp_no <> B.emp_no
AND A.demp_no = B.dept_no;| EmployeeName1 | EmployeeName2 | dept_no |
|---|---|---|
| Evy | Yuna | d002 |
| Yuna | Evy | d002 |
서브쿼리(Subquery)
서브쿼리(Subquery)는 하나의 SQL문안에 존재하는 또 하나의 SQL문을 말한다. 사용되는 위치에 따라 세 가지로 구분할 수 있다.
| 구분 | 내용 |
|---|---|
| SELECT절 | 스칼라 서브쿼리(Scalar subquery) |
| FROM절 | 인라인 뷰(Inline view) |
| WHERE, HAVING절 | 중첩 서브쿼리(Nested subquery) |
Scalar subquery
스칼라 서브쿼리(Scalar subquery)는 주로 SELECT절에 위치하나, ‘컬럼’이 올 수 있는 대부분의 위치에 사용 가능한 서브쿼리이다.
스칼라 서브쿼리는 반드시 하나의 값만을 반환해야 한다.
SELECT 테이블명1.컬럼명,
(SELECT 테이블명2.컬럼명 FROM 테이블명2 WHERE 조건) AS 컬럼명,
테이블명1.컬럼명, …
FROM 테이블명1;Inline view
인라인 뷰(Inline view)는 FROM절 등 ‘테이블’이 올 수 있는 위치에 사용 가능한 서브쿼리이다. 가상의 테이블로서 실제 데이터가 저장되지는 않으며, 임시적으로 생성되는 동적 뷰이다.
SELECT 테이블명1.컬럼명1, 테이블명2.컬럼명1, 테이블명2.컬럼명2
FROM 테이블명1,
(SELECT 컬럼명1, 컬럼명2 FROM 테이블) AS 테이블명2
WHERE 조건;Nested subquery
중첩 서브쿼리(Nested subquery)는 WHERE절과 HAVING절에 사용 가능하며, 몇 기준에 따라 다시 구분이 가능하다.
-
메인 쿼리와의 관계에 따른 구분
구분 내용 비연관(Uncorrelated) 서브쿼리 서브쿼리가 메인 쿼리의 컬럼을 가지고 있지 않은 서브쿼리로, 메인쿼리에 어떤 값을 제공하기 위한 목적으로 주로 사용됨 연관(Correlated) 서브쿼리 서브쿼리가 메인 쿼리의 컬럼을 가지고 있는 형태로, 메인 쿼리가 먼저 수행되어 읽어낸 데이터에 대하여 서브쿼리에서 조건을 설정할 때 주로 사용됨 -
반환되는 데이터 형태에 따른 구분
구분 내용 단일 행 서브쿼리 항상 1건 이하의 실행 결과만을 반환하며, 단일 행 비교 연산자(=, >, <, >= 등)와 함께 사용됨 다중 행 서브쿼리 서브쿼리의 실행 결과가 여러 건이며, 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS 등)와 함께 사용됨 다중 컬럼 서브쿼리 서브쿼리의 실행 결과가 여러 개의 컬럼으로, 서브쿼리와 메인 쿼리에서 비교하고자 하는 컬럼의 개수와 위치는 동일해야 함
참고 자료
- 유선배 SQL개발자(SQLD) 과외노트, 정미나, (주)시대교육
- 관계형 데이터베이스에서 조인(join)이란?
