mysql - SQL 查询左连接表

Question

我昨天确定要实现，并且如果另一个表没有值，则使用LEFT JOIN。
我现在的问题是，如果你不确定哪个表有值(value)，哪个表有值(value)怎么办。

我已经尝试过这个查询。

$query ="SELECT record.student_name,record.student_id,record.student_finger,record.student_section,record.activity_type,record.activity_title,record.score,record.teacher_id,record.activity_id,record.subject_id,attendance.status,attendance.date
        FROM tbl_record record
        LEFT JOIN tbl_attendance attendance on record.student_id=attendance.student_number
        WHERE record.subject_id='$subject_id' and record.teacher_id='$teacher_id' and record.student_section='$section';";  

但不幸的是，如果我的记录表是空的，它不会显示任何内容。

我想要实现的是，
如果table_record为空而tbl_attendance不为空，则会显示tbl_attendance中的记录，
如果table_attendance为空而table_record不为空，则会显示table_record中的记录。

Answer 1

将 LEFT JOIN 更改为 FULL OUTER JOIN，调整 WHERE 子句并让我们知道情况如何。

SELECT 
    record.student_name,
    coalesce(record.student_id, attendance.student_number),
    record.student_finger,
    record.student_section,
    record.activity_type,
    record.activity_title,
    record.score,
    record.teacher_id,
    record.activity_id,
    record.subject_id,
    attendance.status,
    attendance.date
FROM tbl_record record
FULL OUTER JOIN tbl_attendance attendance on record.student_id=attendance.student_number
WHERE (record.subject_id='$subject_id' and record.teacher_id='$teacher_id' 
       and record.student_section='$section') 
   or record.subject_id is null; 

这是一个 MySql 草案版本，不支持 FULL OUTER JOIN。您可以在 RIGHT JOIN 部分中将所有 record.* 字段替换为 NULL:

SELECT 
    record.student_name,
    record.student_id,
    record.student_finger,
    record.student_section,
    record.activity_type,
    record.activity_title,
    record.score,
    record.teacher_id,
    record.activity_id,
    record.subject_id,
    attendance.status,
    attendance.date
FROM tbl_record record
LEFT JOIN tbl_attendance as attendance on 
    record.student_id = attendance.student_number
WHERE (record.subject_id='$subject_id' and record.teacher_id='$teacher_id' 
       and record.student_section='$section') 
UNION
SELECT 
    record.student_name,
    attendance.student_number,
    record.student_finger,
    record.student_section,
    record.activity_type,
    record.activity_title,
    record.score,
    record.teacher_id,
    record.activity_id,
    record.subject_id,
    attendance.status,
    attendance.date
FROM tbl_record as record
RIGHT JOIN tbl_attendance as attendance on 
    record.student_id = attendance.student_number
WHERE record.subject_id is null
;