SQL Join 유형별 예제
-- 예제 테이블 구조
-- users: id, name, email
-- orders: id, user_id, product_name, amount, created_at
-- ============================================
-- INNER JOIN: 두 테이블에 모두 있는 데이터만
-- ============================================
SELECT
u.name,
u.email,
o.product_name,
o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01';
-- 결과: 주문이 있는 사용자만 조회됨
-- ============================================
-- LEFT JOIN: 왼쪽 테이블(users)의 모든 행 포함
-- ============================================
SELECT
u.name,
u.email,
COALESCE(o.product_name, '주문 없음') AS product,
COALESCE(o.amount, 0) AS amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 결과: 주문이 없는 사용자도 NULL 값과 함께 조회됨
-- ============================================
-- 다중 테이블 JOIN
-- ============================================
-- products: id, name, category_id, price
-- categories: id, name
SELECT
u.name AS customer_name,
o.created_at AS order_date,
p.name AS product_name,
c.name AS category_name,
o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id
INNER JOIN categories c ON p.category_id = c.id
WHERE o.amount > 100
ORDER BY o.created_at DESC;
-- ============================================
-- SELF JOIN: 같은 테이블을 자기 자신과 조인
-- ============================================
-- employees: id, name, manager_id
SELECT
e.name AS employee_name,
m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- 결과: 직원과 그 직원의 매니저 이름을 함께 조회
-- ============================================
-- 집계와 함께 사용하는 JOIN
-- ============================================
SELECT
u.name,
COUNT(o.id) AS order_count,
SUM(o.amount) AS total_spent,
AVG(o.amount) AS avg_order_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
HAVING COUNT(o.id) >= 5
ORDER BY total_spent DESC;
Python SQLAlchemy에서 Join 사용
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, func
from sqlalchemy.orm import declarative_base, relationship, Session, joinedload
from sqlalchemy import select
Base = declarative_base()
# 모델 정의
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String(100))
email = Column(String(255))
orders = relationship("Order", back_populates="user")
class Order(Base):
__tablename__ = 'orders'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('users.id'))
product_name = Column(String(255))
amount = Column(Integer)
user = relationship("User", back_populates="orders")
# 데이터베이스 연결
engine = create_engine("postgresql://user:pass@localhost/mydb")
with Session(engine) as session:
# ================================
# 1. 기본 INNER JOIN
# ================================
stmt = (
select(User.name, Order.product_name, Order.amount)
.join(Order, User.id == Order.user_id)
.where(Order.amount > 100)
)
results = session.execute(stmt).all()
for name, product, amount in results:
print(f"{name}: {product} - {amount}원")
# ================================
# 2. LEFT OUTER JOIN
# ================================
stmt = (
select(User.name, Order.product_name)
.outerjoin(Order, User.id == Order.user_id) # LEFT JOIN
)
results = session.execute(stmt).all()
for name, product in results:
product = product or "주문 없음"
print(f"{name}: {product}")
# ================================
# 3. Eager Loading으로 N+1 문제 해결
# ================================
# joinedload: 한 번의 JOIN 쿼리로 관련 데이터를 가져옴
stmt = (
select(User)
.options(joinedload(User.orders))
.where(User.name.like('김%'))
)
users = session.scalars(stmt).unique().all()
for user in users:
print(f"{user.name}의 주문:")
for order in user.orders: # 추가 쿼리 발생하지 않음
print(f" - {order.product_name}")
# ================================
# 4. 집계 함수와 GROUP BY
# ================================
stmt = (
select(
User.name,
func.count(Order.id).label('order_count'),
func.sum(Order.amount).label('total_spent')
)
.outerjoin(Order)
.group_by(User.id, User.name)
.having(func.count(Order.id) >= 3)
.order_by(func.sum(Order.amount).desc())
)
results = session.execute(stmt).all()
for name, count, total in results:
print(f"{name}: {count}건, 총 {total or 0:,}원")
# ================================
# 5. 서브쿼리를 활용한 복잡한 JOIN
# ================================
# 최근 주문이 있는 사용자만 조회
subquery = (
select(Order.user_id)
.where(Order.created_at >= '2024-01-01')
.distinct()
.subquery()
)
stmt = (
select(User)
.join(subquery, User.id == subquery.c.user_id)
)
active_users = session.scalars(stmt).all()