집합 연산자 파헤치기 - 문과생 SQL 독학 시리즈8

"Everything is available basically for free. You can learn anything you want for free." - Elon Musk의 인터뷰 중

머스크의 말처럼 모두 무료로 접할 수 있고 무엇이든 할 수 있으니..

무료로 접할 수 있는 정보들을 제게 맞게 최적화해서 정리해보려고 합니다.

 

이 글에선 집합 연산자에 대해 자세히 알아보겠습니다.

아래는 예시로 사용할 product_a, product_b 테이블입니다.

product_a

product_id	product_name	price	quantity
1	Apple	1.50	10
2	Banana	0.80	30
3	Orange	1.20	20
4	Pear	2.00	15

 

product_b

product_id	product_name	price	quantity
3	Orange	1.20	20
4	Pear	2.00	15
5	Mango	3.00	12
6	Pineapple	4.00	7

 

1. UNION - 중복 행은 제거하고 집합된 모든 결과를 조회

SELECT * FROM product_a
UNION
SELECT * FROM product_b;

해석 > product_a의 모든 데이터와 product_b의 모든 데이터를 중복값만 제외하고 조회해줘

결과)

product_id	product_name	price	quantity
1	Apple	1.50	10
2	Banana	0.80	30
3	Orange	1.20	20
4	Pear	2.00	15
5	Mango	3.00	12
6	Pineapple	4.00	7

 

2. UNION ALL - 중복값도 포함해서 집합된 모든 결과를 조회

SELECT * FROM product_a
UNION ALL
SELECT * FROM product_b;

해석 > product_a의 모든 데이터와 product_b의 모든 데이터를 조회해줘

결과)

product_id	product_name	price	quantity
1	Apple	1.50	10
2	Banana	0.80	30
3	Orange	1.20	20
4	Pear	2.00	15
3	Orange	1.20	20
4	Pear	2.00	15
5	Mango	3.00	12
6	Pineapple	4.00	7

 

3. INTERSECT - 중복값만 조회

SELECT product_name FROM product_a
INTERSECT
SELECT product_name FROM product_b;

해석 > product_a의 모든 데이터와 product_b의 모든 데이터 중에서 데이터가 일치하는 값의 product_name만 조회해줘

결과)

product_name

Orange

Pear

 

mysql에서는 INTERSECT가 없어서 다른 방식으로 중복값을 찾아야 합니다.

INTERSECT 로직 자체가 모든 열의 값을 비교하여 모든게 일치해야 반환하는 개념인데, mysql에서 inner join으로도 값을 구할 수는 있으나.. 열이 엄청 많다고 했을 땐, 그 열들을 하나하나 동일하다는 조건으로 비교해줘야 합니다.

이는 엄청난 비효율임이라 그만큼의 데이터를 처리해야 하는 경우라면 차라리 파이썬으로 들고가는 게 나을 것 같습니다.

 

아래는 mysql에서 비교하는 열이 적을 때, 사용할 수 있을 방법과 파이썬에서 사용할 수 있을 방법입니다.

mysql)

SELECT product_name 
FROM product_a as t1
inner join product_b as t2 on t1.product_id = t2.product_id 
			AND t1.product_name = t2.product_name 
            AND t1.price = t2.price
            AND t1.quantity = t2.quantity

 

파이썬)

import pandas as pd
import mysql.connector

# 데이터베이스 연결 설정
db_connection = mysql.connector.connect(
    host="hostname",
    user="username",
    password="password",
    database="database_name"
)

# 커서 생성
cursor = db_connection.cursor()

# 첫 번째 쿼리 실행
cursor.execute("SELECT * FROM product_a")
result1 = cursor.fetchall()
columns = [column[0] for column in cursor.description]
df1 = pd.DataFrame(result1, columns=columns)

# 두 번째 쿼리 실행
cursor.execute("SELECT * FROM product_b")
result2 = cursor.fetchall()
columns = [column[0] for column in cursor.description]
df2 = pd.DataFrame(result2, columns=columns)

# 두 데이터프레임의 교집합 계산
intersected_df = df1.merge(df2)

# 결과 출력
print(intersected_df)

 

4. EXCEPT 사용(MINUS) - 앞 데이터에서 뒷 데이터를 뺀 값만 조회

SELECT product_name FROM product_a
EXCEPT
SELECT product_name FROM product_b;

해석 > product_a의 모든 데이터와 product_b의 모든 데이터 중에서 모든 데이터가 일치하는 값을 product_a에서 빼고 남은 product_a의 product_name을 조회해줘

결과)

product_id	product_name	price	quantity
1	Apple	1.50	10
2	Banana	0.80	30

 

intersect와 마찬가지로 except도 mysql에서는 사용이 어렵습니다.

이것도 대체할 수 있는 예시를 적어보겠습니다.

 

mysql)

SELECT product_name 
FROM product_a as t1
left join product_b as t2 on t1.product_id = t2.product_id 
			AND t1.product_name = t2.product_name 
            AND t1.price = t2.price
            AND t1.quantity = t2.quantity
WHERE t2.product_id IS NULL

 

파이썬)

import pandas as pd
import mysql.connector

# 데이터베이스 연결 설정
db_connection = mysql.connector.connect(
    host="hostname",
    user="username",
    password="password",
    database="database_name"
)

# 커서 생성
cursor = db_connection.cursor()

# 첫 번째 쿼리 실행
cursor.execute("SELECT * FROM product_a")
result1 = cursor.fetchall()
columns = [column[0] for column in cursor.description]
df1 = pd.DataFrame(result1, columns=columns)

# 두 번째 쿼리 실행
cursor.execute("SELECT * FROM product_b")
result2 = cursor.fetchall()
columns = [column[0] for column in cursor.description]
df2 = pd.DataFrame(result2, columns=columns)

# 두 데이터프레임의 교집합 계산
excepted_df = df1.merge(df2, how='left', indicator=True).query('_merge == "left_only"').drop('_merge', axis=1)

# 결과 출력
print(intersected_df)

 

5. 참고 문헌

통계 교육원 SQL 강의: 데이터 분석을 위한 SQL 입문(2023) - https://sti.kostat.go.kr/coresti/site/main.do

부스트코스 SQL 강의: 기초 데이터 분석을 위한 핵심 SQL - https://www.boostcourse.org/