为什么我没有使用 LEFT JOIN mysql 从左表中获取所有记录

Question

我正在运行两个Mysql查询，第二个查询没有从左表中获取所有记录。

表一-> audit_form_tbl

field id       company_form_id      field_name
1...................42..................A         
2................. 42...................b
3................. 42...................t
4................. 42...................bgf
5................. 42...................bfddf

表二-> audit_form_value_4_tbl

id             field id          company_form_id      field_name
1.................1................. 42................45
2.................3................. 42................50
3.................2................. 42................70

SELECT aft.field_id, 
aft.sort_order, 
aft.type, 
aft.parent_id, 
aft.type, 
aft.field_name 
FROM audit_form_tbl aft 
WHERE aft.type='FH' 
AND aft.company_id = '4' 
AND aft.company_form_id = '42' 
AND aft.parent_id = '0' 
AND aft.status = 1 
ORDER BY sort_order

SELECT aft.field_id, 
aft.sort_order, 
aft.type, 
aft.parent_id, 
aft.type, 
aft.field_name 
avt.field_value, 
FROM audit_form_tbl aft 
LEFT JOIN audit_form_value_4_tbl avt ON aft.field_id=avt.field_id 
WHERE aft.type='FH' 
AND aft.company_id = '4' 
AND aft.company_form_id = '42' 
AND aft.parent_id = '0' 
AND aft.status = 1 
AND avt.form_id='4421579691865' 
ORDER BY sort_order

我想要表一中的所有记录和表二中的匹配记录。

谢谢

Answer 1

SELECT aft.field_id, 
aft.sort_order, 
aft.type, 
aft.parent_id, 
aft.type, 
aft.field_name 
avt.field_value, 
FROM audit_form_tbl aft 
LEFT JOIN audit_form_value_4_tbl avt ON aft.field_id=avt.field_id 
-- insert the condition by right table to ON
AND avt.form_id='4421579691865' 
WHERE aft.type='FH' 
AND aft.company_id = '4' 
AND aft.company_form_id = '42' 
AND aft.parent_id = '0' 
AND aft.status = 1 
-- remove the condition by right table from WHERE 
--  AND avt.form_id='4421579691865' 
ORDER BY sort_order

Answer 2

您在WHERE子句中avt.form_id = '4421579691865'。如果没有要联接的匹配行，则avt.form_idNULL，avt.form_id = '4421579691865'不为真，因此该行将被过滤掉。将其移至ON子句。

...
LEFT JOIN audit_form_value_4_tbl avt
ON aft.field_id = avt.field_id
AND avt.form_id = '4421579691865'
...