[SQL] SQL 활용 - 표준 JOIN과 집합 연산자에 대하여(ANSI)

표준 조인(Standard Join)

 

1. INNER JOIN

Inner Join

SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

2. LEFT JOIN

LeftJoin

 

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

3. RIGHT JOIN

RightJoin

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

4. OUTER JOIN

Full OUTER JOIN

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key

5. LEFT JOIN EXCLUDING INNER JOIN

Left Excluding Join

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

6. RIGHT JOIN EXCLUDING INNER JOIN

Right Excluding Join

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL

7. OUTER JOIN EXCLUDING INNER JOIN

Outer Excluding Join

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL

8. CROSS JOIN (chat gpt에서 예제 가져왔다 ㅎ.ㅎ)

한쪽 테이블의 모든 행들과 다른 테이블들의 모든 행을 조인시킨다. 즉 모든 가능한 조합을 얻을 수 있다.테스트로 사용할 대용량 데이터를 만들기에 적합하다.(6만건 테이블과 7만건 테이블을 cross join하면 42억건의 데이터를 생성할 수 있다. 하지만 그만큼 시스템에 부하를 줄수있으므로 주의하여 사용하여야한다.) 일반적으로 자주 사용되지않는다.

 

product 테이블

product_id	product_name
1	Apple
2	Orange
3	Banana

region 테이블

region_id	region_name
1	Seoul
2	Busan
3	Daegu

SELECT p.product_name, r.region_name
FROM Product p
CROSS JOIN Region r;

 

product_name	region_name
Apple	Seoul
Apple	Busan
Apple	Daegu
Orange	Seoul
Orange	Busan
Orange	Daegu
Banana	Seoul
Banana	Busan
Banana	Daegu

위의 예시를 확인하면 product테이블과 region테이블을 cross join하여 총 3*3개의 데이터를 얻었음을 알 수 있다.

9. SELF JOIN (chat gpt에서 예제 가져왔다 ㅎ.ㅎ)

자기 자신을 조인하는 것이다. 한테이블의 다른 열에 같은 의미를 가지는 컬럼이 있을 경우에 사용할 수 있다.

"Employees"라는 테이블이 있다고 가정해보자.

employee_id	employee_name	manager_id
1	John	3
2	Mike	3
3	Sarah	NULL
4	Tom	1
5	Lisa	2

위의 테이블에서 "manager_id"는 해당 직원의 매니저를 나타내는 외래 키이다. 이 테이블을 사용하여 매니저와 직원 간의 관계를 확인하고자 할 때 self join을 사용할 수 있다.

예를 들어, "Employees" 테이블에서 각 직원의 이름과 매니저의 이름을 함께 출력해보자.

SELECT e.employee_name AS employee, m.employee_name AS manager
FROM Employees e
JOIN Employees m ON e.manager_id = m.employee_id;

employee	manager
John	Sarah
Mike	Sarah
Sarah	NULL
Tom	John
Lisa	Mike

self join의 간단한 예제이며, 실제로는 더 복잡한 조건과 조인을 사용할 수 있다. self join은 계층 구조나 네트워크 관계와 같은 데이터를 처리할 때  유용하다.

 

집합 연산자

1.UNION

두개의 select 문의 결과를 합쳐서 중복을 제거한 결과를 반환한다.

이는 데이터가 커질수록 비용이 큰 연산이 될 수 있다.이미 select된 결과로 union하기 때문에 중복여부의 판단은 조회한 모든 컬럼의 값들이 기준이된다. 

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

2. UNION ALL

두개의 select 문의 결과를 합쳐서 중복을 포함한 결과를 반환한다.

UNION같은 경우 중복제거를 하는 과정이 있지만 UNION ALL은 그렇지 않기 떄문에 훨씬 성능이 좋다. 

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

 

서로 다른 테이블을 union하여 조회 하는경우 같은 유형의 데이터를 다른 테이블에 가지고있다는 의미이므로 모델링 차원에서 통합에 대한 고려를 해야했을 수 있다.   

 

my sql이 내부적으로 union을 처리하는 과정

1. 최종 UNION [ALL | DISTINCT] 결과에 적합한 임시 테이블(Temporary table)을 메모리 테이블로 생성
2. UNION 또는 UNION DISTINCT 의 경우, Temporary 테이블의 모든 컬럼으로 Unique Hash 인덱스 생성

3. 서브쿼리1 실행 후 결과를 Temporary 테이블에 복사
4. 서브쿼리2 실행 후 결과를 Temporary 테이블에 복사
5. 만약 3,4번 과정에서 Temporary 테이블이 특정 사이즈 이상으로 커지면
    Temporary 테이블을 Disk Temporary 테이블로 변경
    (이때 Unique Hash 인덱스는 Unique B-Tree 인덱스로 변경됨)
6. Temporary 테이블을 읽어서 Client에 결과 전송
7. Temporary 테이블 삭제

3.INTERSECT

두개의 select 문의 결과에서 중복되는 행만 반환한다.

SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

4.EXCEPT, MINUS

첫번쨰 select 문의 결과에서 두번째 select 문의 결과에 있는 행을 제외한 결과를 반환한다.

SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

집합연산자를 사용할 경우에는 열의 수와 데이터 유형이 일치해야한다. 그리고 첫번째 쿼리의 컬럼 별칭으로 결과가 반환된다. (두번째 컬럼의 별칭은 적용이 안됨) 또 쿼리의 순서에 따라 결과값이 달라질 수 있다. 

 

참고자료

https://tragramming.tistory.com/74

[Join] ANSI Join & Oracle Join, Join의 종류

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Visual Representation of SQL Joins

http://egloos.zum.com/sweeper/v/3002332

CROSS JOIN / SELF JOIN

https://openlife.tistory.com/354

UNION과 UNION ALL 의 차이 및 주의 사항