select-sql 聯表查詢（兩張表）

sql 聯表查詢（兩張表）

SELECT
    teacher_info.teacher_id,
    tearcher_name,
    teacher_number,
    tearcher_type,
    teacher_image,
    teacher_gender,
    teacher_birthday,
    identity_number,
    company,
    technical_title,
    technical_grade,
    major_type_id,
    major_id,
    courses_offer,
    phone,
    email,
    relative_material,
    LEVEL,
    country,
    province,
    city,
    region
FROM
    teacher_info,
    teacher_region_info
WHERE
    teacher_info.teacher_id = teacher_region_info.teacher_id

上面的語句會讓teacher_region_info表中如果沒有對應的teacher_id就使teacher_info的數據也不顯示出來了，如何更改代碼？

找到了答案：

SELECT
    teacher_info.teacher_id,
    tearcher_name,
    teacher_region_info.city
FROM
    teacher_info,
    teacher_region_info
WHERE
    teacher_info.teacher_id = teacher_region_info.teacher_id
UNION
    SELECT
        teacher_info.teacher_id,
        tearcher_name,
        teacher_region_info.city
    FROM
        teacher_info,
        teacher_region_info
    WHERE
        teacher_info.teacher_id != NULL

最佳回答：

這時候需要用到外連接
把from後面的改成
from teacher_info left join teacher_region_info on teacher_info.teacher_id = teacher_region_info.teacher_id
SQL的四種連接-左外連接、右外連接、內連接、全連接 建議樓主可以去學一下 很有用