SQLAlchemy 多表 Left Outer Join 查詢實作：單表關聯多張表的正確寫法

情境

主表 A 透過外鍵分別關聯表 B、表 C，需以 Left Outer Join 查詢 A 的所有資料並帶出 B、C 的名稱欄位（即使無對應資料也保留 A 的列）。

資料表結構

-- Table A (主表)
aid (PK), bid (FK), cid (FK), a_name

-- Table B
bid (PK), b_name

-- Table C
cid (PK), c_name

SQLAlchemy Core 正確寫法

from sqlalchemy import select, Table, MetaData, create_engine

engine = create_engine('connection_string')
metadata = MetaData()

# 反射或定義表
A = Table('A', metadata, autoload_with=engine)
B = Table('B', metadata, autoload_with=engine)
C = Table('C', metadata, autoload_with=engine)

# 建立查詢：A LEFT JOIN B LEFT JOIN C
stmt = select(A, B.c.b_name, C.c.c_name).select_from(
    A.outerjoin(B, A.c.bid == B.c.bid)
     .outerjoin(C, A.c.cid == C.c.cid)
)

# 執行
with engine.connect() as conn:
    result = conn.execute(stmt)
    for row in result:
        print(row)

關鍵點解析

SQLAlchemy ORM 寫法（關聯關係已定義）

from sqlalchemy.orm import Session

# 假設模型類別已定義 relationship
# class A(Base): bid = Column(Integer, ForeignKey('B.bid')); b = relationship("B")
# class B(Base): ...
# class C(Base): ...

stmt = select(A).options(
    joinedload(A.b).load_only(B.b_name),
    joinedload(A.c).load_only(C.c_name)
)

with Session(engine) as session:
    results = session.execute(stmt).scalars().all()
    for a in results:
        print(a.a_name, a.b.b_name if a.b else None, a.c.c_name if a.c else None)

• ORM 使用 joinedload 產生 LEFT OUTER JOIN（預設 innerjoin=False）。
• load_only 限制只載入需要的欄位。
• 結果為完整 ORM 物件圖，存取關聯屬性自動處理 None 情況。

原生 SQL 對照

SELECT A.aid, A.a_name, B.b_name, C.c_name
FROM A
LEFT JOIN B ON A.bid = B.bid
LEFT JOIN C ON A.cid = C.cid;

常見錯誤

1. 使用 join() 而非 outerjoin()：預設為 INNER JOIN，會過濾掉無對應 B/C 的 A 列。
2. 未明確選取欄位：select(A, B, C) 會產生重複鍵欄位，導致結果映射錯誤。
3. Join 條件寫反：A.c.bid == B.c.bid 正確；寫成 B.c.bid == A.c.bid 雖邏輯相同但慣例以左表欄位在前。

掌握 outerjoin 鏈式呼叫與 select_from，能在 SQLAlchemy Core 中優雅表達複雜多表外部聯結查詢。