JOIN을 활용한 테이블 결합

from google.cloud import bigquery
import pandas as pd
 
# 인증 설정 (서비스 계정 키 사용 시)
# import os
# os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/your-key.json"
 
# BigQuery 클라이언트 생성
client = bigquery.Client(project='your-project-id')
 
# 쿼리 실행 함수
def run_query(query):
    return client.query(query).to_dataframe()

SELECT columns
FROM table_a
[INNER | LEFT | RIGHT | FULL OUTER | CROSS] JOIN table_b
ON table_a.key = table_b.key

테이블 A          테이블 B          INNER JOIN 결과
+----+----+      +----+----+      +----+----+----+
| id | a  |      | id | b  |      | id | a  | b  |
+----+----+      +----+----+      +----+----+----+
| 1  | X  |      | 1  | P  |  →   | 1  | X  | P  |
| 2  | Y  |      | 3  | Q  |      | 3  | Z  | Q  |
| 3  | Z  |      | 4  | R  |      +----+----+----+
+----+----+      +----+----+

SELECT
    o.order_id,
    o.user_id,
    o.status,
    o.created_at,
    oi.product_id,
    oi.sale_price
FROM `your-project-id.thelook_ecommerce.orders` o
INNER JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Complete'
LIMIT 10

테이블 A          테이블 B          LEFT JOIN 결과
+----+----+      +----+----+      +----+----+------+
| id | a  |      | id | b  |      | id | a  | b    |
+----+----+      +----+----+      +----+----+------+
| 1  | X  |      | 1  | P  |  →   | 1  | X  | P    |
| 2  | Y  |      | 3  | Q  |      | 2  | Y  | NULL |
| 3  | Z  |      | 4  | R  |      | 3  | Z  | Q    |
+----+----+      +----+----+      +----+----+------+

SELECT
    u.id AS user_id,
    u.first_name,
    u.last_name,
    u.email,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(oi.sale_price), 0) AS total_spent
FROM `your-project-id.thelook_ecommerce.users` u
LEFT JOIN `your-project-id.thelook_ecommerce.orders` o
    ON u.id = o.user_id
LEFT JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON o.order_id = oi.order_id
GROUP BY u.id, u.first_name, u.last_name, u.email
ORDER BY total_spent DESC
LIMIT 15

SELECT
    u.id AS user_id,
    u.first_name,
    u.last_name,
    u.email,
    u.created_at AS signup_date
FROM `your-project-id.thelook_ecommerce.users` u
LEFT JOIN `your-project-id.thelook_ecommerce.orders` o
    ON u.id = o.user_id
WHERE o.order_id IS NULL
ORDER BY u.created_at DESC
LIMIT 10

-- RIGHT JOIN (실무에서는 LEFT JOIN을 더 많이 사용)
SELECT
    o.order_id,
    o.status,
    u.first_name,
    u.last_name
FROM `your-project-id.thelook_ecommerce.users` u
RIGHT JOIN `your-project-id.thelook_ecommerce.orders` o
    ON u.id = o.user_id
LIMIT 10

-- 모든 사용자와 모든 주문 (매칭 여부 상관없이)
SELECT
    u.id AS user_id,
    u.first_name,
    o.order_id,
    o.status
FROM `your-project-id.thelook_ecommerce.users` u
FULL OUTER JOIN `your-project-id.thelook_ecommerce.orders` o
    ON u.id = o.user_id
WHERE u.id IS NULL OR o.order_id IS NULL  -- 매칭 안 된 것만
LIMIT 10

-- 프로모션과 카테고리의 모든 조합
WITH promotions AS (
    SELECT promotion_name FROM UNNEST(['Black Friday', 'Summer Sale', 'New Year']) AS promotion_name
),
categories AS (
    SELECT DISTINCT category
    FROM `your-project-id.thelook_ecommerce.products`
    LIMIT 5
)
SELECT
    p.promotion_name,
    c.category,
    CONCAT(p.promotion_name, ' - ', c.category) AS campaign_name
FROM promotions p
CROSS JOIN categories c
ORDER BY p.promotion_name, c.category

SELECT
    o.order_id,
    o.created_at AS order_date,
    o.status,
    u.first_name || ' ' || u.last_name AS customer_name,
    u.country,
    p.name AS product_name,
    p.category,
    p.brand,
    oi.sale_price,
    p.retail_price,
    ROUND(oi.sale_price - p.cost, 2) AS profit
FROM `your-project-id.thelook_ecommerce.orders` o
INNER JOIN `your-project-id.thelook_ecommerce.users` u
    ON o.user_id = u.id
INNER JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON o.order_id = oi.order_id
INNER JOIN `your-project-id.thelook_ecommerce.products` p
    ON oi.product_id = p.id
WHERE o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 15

-- ❌ 나쁜 예: JOIN 후 필터링
SELECT *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2024-01-01'
 
-- ✅ 좋은 예: 서브쿼리로 먼저 필터링
SELECT o.order_id, o.status, oi.sale_price
FROM (
    SELECT order_id, status
    FROM orders
    WHERE created_at >= '2024-01-01'
) o
JOIN order_items oi ON o.order_id = oi.order_id

-- 쿼리 앞에 EXPLAIN 추가
EXPLAIN
SELECT o.order_id, COUNT(oi.id)
FROM `your-project-id.thelook_ecommerce.orders` o
JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON o.order_id = oi.order_id
GROUP BY o.order_id

필요한 데이터가?
├─ 양쪽 모두 있어야 함 → INNER JOIN
├─ 한쪽은 전부, 다른 쪽은 있으면 → LEFT/RIGHT JOIN
├─ 양쪽 전부 다 필요 → FULL OUTER JOIN
└─ 모든 조합 필요 → CROSS JOIN