您需要一个完整的外部连接,但不幸的是,MySQL不支持这种连接。幸运的是,通过使用
UNION ALL
:
更新:更改查询以回答OP更新的问题。
SELECT
COALESCE(school_day, college_day) AS day,
COALESCE(school_open_time, college_open_time) AS school_open_time,
COALESCE(school_close_time, college_close_time) AS school_close_time,
COALESCE(college_open_time, school_open_time) AS college_open_time,
COALESCE(college_close_time, school_close_time) AS college_close_time
FROM (
SELECT * FROM table_school LEFT JOIN table_college ON school_day = college_day
UNION ALL
SELECT * FROM table_school RIGHT JOIN table_college ON school_day = college_day
WHERE school_day IS NULL
) AS T1