[BigQuery] Left Join using ON or WHERE

jaytiger·2021년 12월 26일
0

▷ 테이블 조인

개요

팀내 쿼리 리뷰를 하다보면 두 테이블을 결합(JOIN)하는 과정에서 선택(필터링) 조건을 ON 절에 기술하는 경우를 가끔 만나게 된다. 사소하긴 하지만 주의를 요하는 경우가 있어 간략히 정리하고자 한다.

결합(JOIN)방식에 따라서 필터링 조건을 ON절에 기술하는 경우와 WHERE절에 기술하는 경우에 따라 결과 테이블이 달라지기도 하므로 차이를 명확히 이해하는 것이 중요하다.

ON 에는 결합 조건, WHERE 에는 선택(필터링) 조건을 작성한다.

일반적으로 위의 기준에 따라 JOIN을 포함한 쿼리문이 작성되어야 하지만,

  1. 콤마(,) 조인 또는 CROSS JOINWHERE절에 결합 조건을 사용하고 있고,
  2. 추가적인 WHERE 절을 생략하기 위해서 ON 절에 조건을 작성하는 등,

결합 조건과 선택 조건이 ONWHERE절 구별없이 사용되는 경우가 있어 간혹 원치 않은 결과가 만들어지기도 한다.

예시

요구사항이 다음과 같은 분석 요청이 있다고 하자.

아래 2개 테이블, documentsdownloads 에서 1, 2, 4 문서를 다운로드 한 사용자를 확인하고자 한다. 다운로드 이력이 없는 경우에도 관심 문서의 모든 행이 출력되어야 한다.

우선 INNER JOIN 의 경우 4번 문서의 다운로드 이력이 없기 때문에 최종 결과에서 4번 문서와 관련된 row가 모두 제외되므로 요구사항에 부합하지 않는다.

다음은 LEFT JOIN을 포함하는 2개의 쿼리를 살펴보자.

DECLARE documents ARRAY<STRUCT<id INT64, name STRING>> DEFAULT [
  (1, 'doc1'), (2, 'doc2'), (3, 'doc3'), (4, 'doc4'), (5, 'doc5')
];

DECLARE downloads ARRAY<STRUCT<id INT64, did INT64, user STRING>> DEFAULT [
  (1, 1, 'sandeep'), (2, 1, 'simi'), (3, 2, 'sandeep'), (4, 2, 'reya'), (5, 3, 'simi')
];

-- Ex1.
SELECT l.*, r.* EXCEPT(id)
FROM UNNEST(documents) l LEFT JOIN UNNEST(downloads) r
ON l.id = r.did AND l.id IN (1, 2, 4)
;

-- Ex2.
SELECT l.*, r.* EXCEPT(id)
FROM UNNEST(documents) l LEFT JOIN UNNEST(downloads) r ON l.id = r.did 
WHERE l.id IN (1, 2, 4)
;

Ex1 쿼리는 ON절에 테이블 결합 조건과 필터링 조건을 모두 기술하고 있다. 반면 Ex2 쿼리는 ON절에는 결합조건만 기술하고 WHERE절에 필터링 조건을 기술하였다.

결과를 확인해 보면 오른쪽이 요구사항에 부합하는 결과임을 알 수 있다.

LEFT JOIN에서 ON절의 조건이 참일 때에 한해서 오른쪽 테이블의 컬럼값을 취한다. (ON 절이 참이 아니어도 왼쪽 테이블의 행이 필터링되지 않는다)

Ex1ON절에 있는 l.id IN (1, 2, 4) 조건은 필터링으로 동작하지 않고 오른쪽 테이블의 컬럼값을 취할 지 여부를 결정하는 결합조건으로 사용되기 때문에 왼쪽 테이블의 doc3이나 doc4이 최종 결과셋에 그대로 남게된다.

따라서 원하는 목적을 위해서는 Ex2처럼 해당 조건을 ON 절이 아닌 WHERE 절에 기술해줘야 한다.

(update '22/04/30)
PAP 커뮤니티의 아래 블로그에 이 주제와 관련된 내용을 다루고 있으니 참고하자.

LEFT JOIN 후 RIGHT TABLE에 대한 조건을 추가하면 OUTER JOIN의 효과가 사라진다.

블로그의 내용 중 Outer Join의 효과가 사라진다는 표현이 의미있게 다가와서 인용하였다.

-- B)
SELECT u.id, u.name, s.payment_cycle
  FROM user AS u
  LEFT JOIN (
    SELECT user_id, payment_cycle
      FROM subscription
     WHERE payment_cycle = 'Monthly'
  ) AS s
    ON a.id = s.application

참고로 PAP 블로그내 위 예시는 BigQuery 에서는 ON 절에 결합조건을 추가 기술함으로써 서브쿼리 없이 간결하게 작성할 수 있다.

-- B)
SELECT u.id, u.name, s.payment_cycle
  FROM user AS u
  LEFT JOIN subscription AS s
    ON a.id = s.application AND s.payment_cycle = 'Monthly'

(update, '22/04/30')

Mode.com 에서도 비슷한 주제의 내용을 발견할 수 있다.

글의 요지는 필터링 조건을 ON 절에 기술할 경우 JOIN이 이루어지기 전의 테이블에 대해 필터링이 먼저 수행이 되는 반면 WHERE 절에 기술할 경우 JOIN이 이루어진 테이블에 대해서 필터링이 이루진다는 내용이다.

요약

LEFT JOIN에서 왼쪽 테이블을 필터링할 목적으로 ON절에 조건을 주는 경우는 원하지 않는 결과가 나올 수 있다. 그러므로, JOIN 이후의 WHERE 절에 조건을 기술하던가 아니면 사전에 서브쿼리의 WHERE절을 통해 LEFT 테이블의 크기를 줄이는 것이 좋다.

ON 절에 양쪽 테이블의 JOIN Key 값을 비교하는 결합 조건 외에 특정 테이블 (LEFT 또는 RIGHT 테이블) 컬럼의 필터링 조건을 결합 조건으로 제시하는 경우 JOIN 전 테이블에 대해 필터링이 먼저 이루어진다.

ex) ON a.id = s.application AND s.payment_cycle = 'Monthly'
--> 여기서 s.payment_cycle = 'Monthly' 는 Right Table에 대해 이 조건으로 필터링된 결과가 JOIN 되도록 만든다.

profile
Jaytiger

0개의 댓글