Union, Join

밤비나·2023년 4월 17일

sql

SQL

목록 보기

5/13

Union

union

UNION은 두 개 이상의 SELECT 문의 결과를 결합하는 데 사용되는 SQL 연산자이다. UNION 연산자를 사용하면 여러 테이블에서 데이터를 가져와서 하나의 결과 집합으로 만들 수 있다.

예제) e-commerce 사이트에서 회원 가입 및 주문 내역 관련 정보를 담은 두 개의 테이블이 있다고 가정해보겠다. 첫 번째 테이블은 members이고 두 번째 테이블은 orders이다.

members

+-------+-------------+----------+----------------------------+
| id    | name        | email           | join_date           |
+-------+-------------+----------+----------------------------+
| 1     | John Smith  | john@gmail.com  | 2021-01-01 00:00:00 |
| 2     | Alice Brown | alice@gmail.com | 2021-02-01 00:00:00 |
| 3     | Bob Lee     | bob@gmail.com   | 2021-03-01 00:00:00 |
+-------+-------------+----------+----------------------------+

orders

+-------+------------+---------------------+--------+
| id    | member_id | order_date           | amount |
+-------+------------+---------------------+--------+
| 1     | 1          | 2021-01-05 00:00:00 | 100    |
| 2     | 1          | 2021-02-15 00:00:00 | 200    |
| 3     | 2          | 2021-03-20 00:00:00 | 150    |
+-------+------------+---------------------+--------+

이 두 개의 테이블을 합쳐서 members 테이블과 orders 테이블의 모든 정보를 담은 테이블을 생성

SELECT m.id, m.name, m.email, o.order_date, o.amount
FROM members m
LEFT JOIN orders o
ON m.id = o.member_id

UNION

SELECT m.id, m.name, m.email, NULL AS order_date, NULL AS amount
FROM members m
LEFT JOIN orders o
ON m.id = o.member_id
WHERE o.id IS NULL;

위 쿼리는 members 테이블과 orders 테이블을 조인한 후, UNION을 이용하여 결과를 결합한다. 첫 번째 SELECT 문에서는 members와 orders를 조인한 결과를 반환하고, 두 번째 SELECT 문에서는 members와 orders를 조인했지만, orders에 매칭되는 결과가 없는 경우, null 값을 반환한다.

이를 통해 members 테이블과 orders 테이블을 전체적으로 조회할 수 있으며, orders 테이블에 매칭되는 데이터가 없는 members 테이블의 데이터도 조회할 수 있다.

union all

UNION ALL은 UNION과 유사하지만 중복된 데이터도 포함하여 결과를 반환하는 연산자이다. 따라서 UNION보다 더 빠른 속도로 결과를 반환할 수 있다.

예제) orders와 payments 테이블에서 각각 주문일자와 결제일자를 추출하여 합치기

SELECT order_date AS date, 'order' AS type FROM orders
UNION ALL
SELECT payment_date AS date, 'payment' AS type FROM payments;

결과적으로는 주문일자와 결제일자가 모두 포함된 결과가 반환된다. 만약 중복된 데이터가 있어도 모두 결과에 포함된다.

+------------+--------+
| date       | type   |
+------------+--------+
| 2023-03-01 | order  |
| 2023-03-03 | order  |
| 2023-03-04 | order  |
| 2023-03-01 | payment|
| 2023-03-02 | payment|
| 2023-03-04 | payment|
+------------+--------+

JOIN

inner join

Inner Join은 두 개 이상의 테이블에서 데이터를 가져와서 서로 관련 있는 열(Column)을 기준으로 일치하는 행(Row)만을 결과로 반환하는 SQL JOIN 방법 중 하나이다.

예제) 고객과 주문 테이블에서 일치하는 정보를 가져오기

SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

"customers" 테이블과 "orders" 테이블을 INNER JOIN 하여, "customer_id" 열이 일치하는 경우에 대해 모든 열을 반환한다. 이렇게 함으로써, 고객의 정보와 해당 고객의 주문 정보를 모두 볼 수 있게 된다.

Inner Join은 다른 Join 방법과 함께 사용할 수도 있다. 예를 들어, Outer Join과 함께 사용하면 특정 테이블에서 일치하지 않는 데이터도 모두 가져올 수 있다.

left join & right join

Left Join과 Right Join은 Inner Join과 마찬가지로 두 개 이상의 테이블에서 데이터를 조합하는 방법 중 하나이다. 하지만 Inner Join과 다르게 특정 테이블을 중심으로 데이터를 가져오는 방법이 다르다.

Left Join은 기준이 되는 왼쪽 테이블의 모든 데이터를 가져오면서, 오른쪽 테이블에서 조건에 맞는 데이터가 있으면 가져오는 방식이다. 만약 오른쪽 테이블에서 조건에 맞는 데이터가 없으면, 해당 결과는 NULL로 반환된다. Left Join을 사용할 때는 Left Join으로 지정한 왼쪽 테이블과 오른쪽 테이블 모두에 존재하는 컬럼을 사용하는 것이 좋다.

예제) customers 테이블과 orders 테이블을 Left Join하여, customers 테이블의 모든 데이터를 가져오면서 orders 테이블에서 조건에 맞는 데이터가 있으면 함께 가져오기

SELECT *
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Right Join은 Left Join과 반대로 오른쪽 테이블을 중심으로 동작합니다. 즉, 오른쪽 테이블의 모든 데이터를 가져오면서, 왼쪽 테이블에서 조건에 맞는 데이터가 있으면 함께 가져오는 방식이다. 만약 왼쪽 테이블에서 조건에 맞는 데이터가 없으면, 해당 결과는 NULL로 반환한다. Right Join을 사용할 때는 Right Join으로 지정한 오른쪽 테이블과 왼쪽 테이블 모두에 존재하는 컬럼을 사용하는 것이 좋다.

SELECT *
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

self join

Self join은 하나의 테이블에서 자기 자신을 조인하는 것이다. 주로 테이블 안에 포함된 부모-자식 관계를 모델링하는 데 유용하다.

예제 1. 부하직원 찾기

SELECT 
  a.employee_id, 
  a.name AS employee_name, 
  b.name AS manager_name
FROM employee a
INNER JOIN employee b
ON a.manager_id = b.employee_id;

예제 2. 나이가 같은 사람들 찾기

SELECT 
  a.name AS person1, 
  b.name AS person2,
  a.age
FROM person a
INNER JOIN person b
ON a.age = b.age AND a.person_id < b.person_id;

outer join

Outer Join은 두 개의 테이블에서 데이터를 조합할 때, 일치하지 않는 데이터를 포함시키는 JOIN 방식이다. 일치하지 않는 데이터가 있는 경우에는 NULL값으로 출력된다. Outer Join은 다음과 같이 3가지 종류가 있다.

Left Outer Join
Right Outer Join
Full Outer Join

SELECT m.name, o.order_date, o.amount
FROM members m
LEFT OUTER JOIN orders o ON m.member_id = o.member_id;

SELECT m.name, o.order_date, o.amount
FROM orders o
RIGHT OUTER JOIN members m ON m.member_id = o.member_id;

members 테이블에는 회원 정보가 모두 저장되어 있지만, orders 테이블에는 회원이 아닌 고객의 주문 정보도 함께 저장되어 있다. 이럴 때, LEFT OUTER JOIN을 사용하여 회원 정보와 주문 정보를 연결할 수 있다. LEFT OUTER JOIN은 JOIN 조건과 일치하지 않는 데이터도 결과에 포함시켜 출력한다.

반대로, RIGHT OUTER JOIN은 JOIN 조건과 일치하지 않는 데이터도 결과에 포함시켜 출력하지만, 오른쪽 테이블을 기준으로 출력한다. RIGHT OUTER JOIN을 사용하는 경우는 그리 많지 않지만, 필요할 때 사용할 수 있다.
주문 정보와 해당 주문을 발생시킨 회원 정보를 함께 조회한다. 만약 주문을 한 번도 하지 않은 회원이 있어도 결과에 포함되어 출력한다.

full outer join

MySQL에서는 FULL OUTER JOIN 구문을 지원하지 않기 때문에, LEFT JOIN과 RIGHT JOIN을 UNION으로 연결하는 방법으로 FULL OUTER JOIN을 대체할 수 있다.

예제 ) members와 orders 테이블이 있을 때, members와 orders 테이블의 모든 데이터를 포함하여 조회

SELECT *
FROM members
LEFT JOIN orders ON members.id = orders.member_id
UNION
SELECT *
FROM members
RIGHT JOIN orders ON members.id = orders.member_id
WHERE members.id IS NULL;