mysql - LEFT/RIGHT JOIN 不返回空行

Question

我有一个查询，它在 2 个表上使用自连接来返回单行结果。我遇到的问题是，如果 seat1 是空的，我什么也得不到。

SELECT seat1.seat_type_id
     , seat1.seat_type_qty
     , seat2.seat_type_id
     , seat2.seat_type_qty
  FROM jos_sv_apptpro2_requests AS R 
  LEFT JOIN jos_sv_apptpro2_seat_counts AS seat1 ON R.id_requests = seat1.request_id 
  LEFT JOIN jos_sv_apptpro2_seat_counts AS seat2 ON R.id_requests = seat2.request_id
 WHERE (seat1.seat_type_id = 6 AND seat2.seat_type_id = 7)
   AND R.id_requests = 8703
   AND R.resource = 3

这应该返回:

seat_type_id 6
seat_type_qty 0 <= this is the empty row
seat_type_id1 7
seat_type_qty 1

Answer 1

WHERE seat1.seat_type_id = 6 AND seat2.seat_type_id = 7

是删除所有具有 NULL 值的行。您应该将这些条件移动到 JOIN 条件，以便 RDBMS 执行您期望的操作:

LEFT JOIN jos_sv_apptpro2_seat_counts AS seat1 ON R.id_requests = seat1.request_id 
                                              AND seat1.seat_type_id = 6
LEFT JOIN jos_sv_apptpro2_seat_counts AS seat2 ON R.id_requests = seat2.request_id
                                              AND seat2.seat_type_id = 7