MySQL ‐ Why You Should Use MySQL: JOIN - thought-corner/Backend-PlayGround GitHub Wiki

MySQL - Why You Should Use MySQL: JOIN

Nested Loop Join and Matched Functions

Nested Loop Join(중첩 루프 조인)은 이름 그대로 "이중 FOR문"과 동일한 방식으로 작동하는 가장 기본적인 JOIN 알고리즘이다.
Driving Row : 외부 루프를 도느 테이블
Driven Table : 내부 루프를 도는 테이블
시간 복잡도와 비효율성 : 두 테이블의 행이 각각 N, M개라면 기본적으로 O(N x M)을 가진다. 데이터가 커질수록 기하급수적으로 느려지기 때문에, 이를 최적화하기 위해 인덱스를 타고 검색하는 Index Nested Loop Join이나 메모리 버퍼를 활용하는 Block Nested Loop Join 등으로 발전하게 된다.

-- Nested Loop Join
FOR EACH row_left IN left_table:
    matched = false

    FOR EACH row_right IN right_table:
        IF match_function(row_left, row_right) IS TRUE:
            OUTPUT (row_left + row_right)
            matched = true
    IF matched = false:
        OUTPUT (row_left + NULL)

-- ON : Match Function
SELECT u.id, u.name, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;

ON u.id = p.user_id 부분이 바로 알고리즘 내부의 match_function(row_left, row_right) 역할을 한다.
이진 상태 평가 : Match Function은 두 테이블의 현재 행을 입력받아 결합 조건이 일치하는지 True 또는 False로만 반환된다.

Handling NULLs in LEFT JOIN and WHERE vs ON Execution Plans

FOR EACH row_left IN users:
    matched = false
    FOR EACH row_right IN posts:
        IF match_function(row_left, row_right):  -- ← ON 절이 여기서 평가
            OUTPUT (row_left + row_right)
            matched = true
    IF matched = false:
        OUTPUT (row_left + NULL)  -- ← NULL 패딩은 ON 절 평가 후

❓ON 절과 WHERE 절의 평가 시점(실행 순서) 차이

ON절의 경우 조인하는 동안 평가를 하게 되고 WHERE절의 경우 조인이 다 끝난 후 평가를 하게 된다.

Subquery Unnesting, CTEs, and AST Concepts

SELECT
    u.id,
    u.name,
    COUNT(p.id) AS post_count,
    COUNT(c.id) AS comment_count
FROM users u
    LEFT JOIN posts p ON u.id = p.user_id         -- u.id = 1인 사람이 게시글 2개
    LEFT JOIN comments c ON u.id = c.user_id      -- u.id = 1인 사람이 댓글 3개
GROUP BY u.id, u.name;

데이터베이스는 GROUP BY로 숫자를 세기 전에 먼저 LEFT JOIN을 두 번 수행하여 데이터를 하나로 합친다. 이 때, u.id=1이 작성한 게시글 2개와 댓글 3개가 곱해지면서 총 6줄의 가상 데이터(임시 테이블)가 만들어진다.
집계(COUNT)의 왜곡 : 이제 데이터베이스는 6개의 데이터 위에서 집계 함수를 실행하는데 데이터가 이미 복사되어 늘어나 있으므로, 게시글 수와 댓글 수 모두 6으로 잘못 계산되어 버린다.

-- CTE
WITH user_base AS (
    SELECT id, name FROM users
),
     post_stats AS (
         SELECT user_id, COUNT(*) AS post_count
         FROM posts
         GROUP BY user_id
     ),
     comment_stats AS (
         SELECT user_id, COUNT(*) AS comment_count
         FROM comments
         GROUP BY user_id
     )
SELECT
    u.id,
    u.name,
    COALESCE(p.post_count, 0) AS post_count, # 2
    COALESCE(c.comment_count, 0) AS comment_count # 3
FROM user_base u
         LEFT JOIN post_stats p ON u.id = p.user_id
         LEFT JOIN comment_stats c ON u.id = c.user_id;

위와 같이 CTE 쿼리는 먼저 다 세어놓고(COUNT) 나중에 이어 붙인다(JOIN)
각각의 1 : N 테이블을 GROUP BY로 요약해서 1 : 1 관계로 차원 축소 시킨 뒤에 메인 테이블에 조인하는 것이 실무에서 다중 1 : N 통계를 낼 때 사용하는 가장 정확하고 정석적인 방법이다.

❓LEFT JOIN 후 WHERE절에서 오른쪽 테이블 컬럼에 조건을 적용한다면?

LEFT JOIN 후 WHERE절에서 오른쪽 테이블의 컬럼에 조건을 걸면, NULL값은 3-Valued Logic에 의해 FALSE로 처리되어 걸러진다.
따라서 LEFT JOIN으로 얻었던 NULL 패딩 행들이 사라져 INNER JOIN처럼 동작하게 될 수 있다.