MySQL mysql左外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3058834/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql left outer join
提问by tirso
I have two tables:
我有两个表：
employeewith fields employee_id, firstname, middlename, lastnametimecardwith fields employee_id,time-in,time-out,tc_date_transaction
employee与字段employee_id，名字，中间名，姓氏timecard与字段employee_id,time-in,time-out,tc_date_transaction
I want to select all employee records which have the same employee_id with timecard and date is equal with the current date. If there are no records equal with the current date then return also the records of employee even without time-in,timeout and tc_date_transaction.
我想选择所有具有相同员工 ID 和时间卡并且日期与当前日期相等的员工记录。如果没有与当前日期相等的记录，则即使没有 time-in、timeout 和 tc_date_transaction 也返回员工的记录。
I have a query like this
我有一个这样的查询
SELECT *
FROM employee LEFT OUTER JOIN timecard
ON employee.employee_id = timecard.employee_id
WHERE tc_date_transaction = "17/06/2010";
result should like this:
结果应该是这样的：
employee_id | firstname | middlename | lastname | time-in | time-out | tc_date_transaction
------------------------------------------------------------------------------------------
1 | john | t | cruz | 08:00 | 05:00 | 17/06/2010
2 | mary | j | von | null | null | null
回答by Loxley
You are filtering tc_date_transaction which filters all null values in this field, even those generated by the outer-join and therefore defeats its purpose. Move the filter "tc_date_transaction = "17/06/2010"" into the join clause and it will work.
您正在过滤 tc_date_transaction 过滤此字段中的所有空值，即使是由外连接生成的值，因此违背了其目的。将过滤器 "tc_date_transaction = "17/06/2010"" 移动到 join 子句中，它将起作用。
SELECT *
FROM employee LEFT OUTER JOIN timecard
ON employee.employee_id = timecard.employee_id and tc_date_transaction = "17/06/2010";
or write
或写
SELECT *
FROM employee LEFT OUTER JOIN timecard
ON employee.employee_id = timecard.employee_id
where (tc_date_transaction = "17/06/2010" or tc_date_transaction is null);
