MySQL 兩表關聯查詢：LEFT JOIN 獲取寶寶所有疫苗狀態（含未接種）

表結構假設

-- 寶寶信息表 (已接種記錄)
CREATE TABLE baby_vaccine_record (
    id INT PRIMARY KEY AUTO_INCREMENT,
    baby_id INT NOT NULL,
    vaccine_id INT NOT NULL,
    is_vaccined TINYINT DEFAULT 1, -- 1=已接種
    vaccined_date DATE,
    INDEX idx_baby (baby_id)
);

-- 疫苗基礎信息表 (所有疫苗)
CREATE TABLE vaccine_info (
    vaccine_id INT PRIMARY KEY,
    vaccine_name VARCHAR(100),
    vaccine_type VARCHAR(50),
    recommended_age VARCHAR(50)
);

目標：查詢指定寶寶的所有疫苗狀態

結果需包含：疫苗名稱、類型、推薦年齡、**是否已接種 (是/否)**、接種日期 (未接種為 NULL)。

標準 SQL (LEFT JOIN)

SELECT 
    vi.vaccine_id,
    vi.vaccine_name,
    vi.vaccine_type,
    vi.recommended_age,
    CASE WHEN bvr.is_vaccined = 1 THEN '已接種' ELSE '未接種' END AS status,
    bvr.vaccined_date
FROM vaccine_info vi
LEFT JOIN baby_vaccine_record bvr
    ON vi.vaccine_id = bvr.vaccine_id
   AND bvr.baby_id = 12345  -- 目標寶寶 ID
ORDER BY vi.recommended_age; -- 或按 vaccine_id 排序

關鍵點解析

等效子查詢寫法 (性能通常較差)

SELECT 
    vi.*,
    (SELECT '已接種' FROM baby_vaccine_record bvr 
     WHERE bvr.vaccine_id = vi.vaccine_id AND bvr.baby_id = 12345 AND bvr.is_vaccined = 1
     LIMIT 1) AS status,
    (SELECT vaccined_date FROM baby_vaccine_record bvr 
     WHERE bvr.vaccine_id = vi.vaccine_id AND bvr.baby_id = 12345
     LIMIT 1) AS vaccined_date
FROM vaccine_info vi;

Java/MyBatis 映射示例

<select id="selectVaccineStatusByBabyId" resultType="VaccineStatusVO">
    SELECT vi.vaccine_id, vi.vaccine_name, vi.vaccine_type, vi.recommended_age,
           CASE WHEN bvr.is_vaccined = 1 THEN '已接種' ELSE '未接種' END AS status,
           bvr.vaccined_date
    FROM vaccine_info vi
    LEFT JOIN baby_vaccine_record bvr
        ON vi.vaccine_id = bvr.vaccine_id
       AND bvr.baby_id = #{babyId}
    ORDER BY vi.vaccine_id
</select>

常見錯誤

用 INNER JOIN

只返回已接種疫苗，丟失未接種信息。

WHERE bvr.baby_id = 12345

同 INNER JOIN 效果，因 NULL 不等於任何值。

未處理重複接種記錄

若同一疫苗有多條記錄，需在 JOIN 前去重或聚合 (如取最新日期)。