(SQL) FULL OUTER JOIN, SELF JOIN과 비교 연산자를 사용한 JOIN 총정리

지난 글에서 LEFT, RIGHT, INNER JOIN 에 대해 알아보았습니다. 이번 글에서는 FULL OUTER JOIN, SELFT JOIN과 비교 연산자를 사용한 JOIN에 대해 알아보겠습니다.

 

🔻(참고)LEFT,RIGHT,INNER JOIN🔻

(SQL) JOIN, LEFT JOIN, RIGHT JOIN 정리 및 연습문제

1. FULL OUTER JOIN

• FULL OUTER JOIN 을 벤다이어그램으로 직관적으로 살펴보면 아래와 같습니다.

출처 ❘ Udacity

SELECT column_name(s)
FROM table_A
FULL OUTER JOIN table_B
ON table_A.comlumn_name_A = table_B.column_name_B;

• 또한, 아래의 벤다이어그램과 같이 일치하지 않는 행만 반환하려는 경우 쿼리의 끝에 다음 줄을 추가하면 됩니다.

출처 ❘ Udacity

WHERE table_A.column_name_A IS NULL OR table_B.colume_name_B IS NULL

 

 

2. 비교 연산자를 사용한 JOIN

• 예시를 들어 설명해보겠습니다. 
• 먼저, 각 계정의 첫 번째 주문을 반환하는 쿼리를 작성해보겠습니다.

SELECT *
FROM orders
WHERE DATE_TRUNC('month', occurred_at) = 
	(SELECT DATE_TRUNC('month', MIN(occurred_at)) 
   	 FROM orders)
ORDER BY occurred_at

• 이제 부등 JOIN 을 사용하여 웹 이벤트 전체 테이블을 JOIN 해보겠습니다.

SELECT orders.id, orders.occurred_at AS order_date, events.* 
FROM orders 
LEFT JOIN web_events events 
ON events.account_id = orders.account_id 
AND events.occurred_at < orders.occurred_at 
WHERE DATE_TRUNC('month', orders.occurred_at)= 
	(SELECT DATE_TRUNC('month', MIN(orders.occurred_at)) 
        FROM orders) 
ORDER BY orders.account_id, orders.occurred_at

 

3. SELF JOIN

• 테이블 자체를 JOIN 하는 것이 유용할 때도 있습니다.
• 예를 들어, 28일 이내에 여러 주문을 한 계정을 알고 싶다고 가정해보겠습니다.
• 쿼리는 다음과 같습니다.

SELECT o1.id AS o1_id, 
    o1.account_id AS o1_account_id,
    o1.occurred_at AS o1_occurred_at, 
    o2.id AS o2_id, 
    o2.account_id AS o2_account_id, 
    o2.occurred_at AS o2_occurred_at 
FROM orders o1 
LEFT JOIN orders o2 
ON o1.account_id = o2.account_id 
AND o2.occurred_at > o1.occurred_at 
AND o2.occurred_at <= o1.occurred_at + INTERVAL '28 days' 
ORDER BY o1.account_id, o1.occurred_at

• 먼저, o1이라는 레이블이 붙은 첫 번째 주문 테이블에서 계정 ID를 찾아 두 번째 주문 테이블 o2의 계정의 ID와 비교한 다음 JOIN 하고 다른 별칭을 부여합니다.
• o2의 레코드가 o1의 레코드 이후 28일 이내에 있기를 원하기 때문에 JOIN 절에 두 개의 부등식을 설정합니다.
  • 1) o2.occurred_at이 o1.occurred_at 보다 큰 곳에 JOIN => 원래 주문이 이루어진 후 발생한 주문 찾기
  • 2) o1.occurred_at 이후 28일 동안 o2.occurred_at이 작거나 같은 주문 찾기 => 시간 제한 두기