MySQL 左连接子查询 *
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19553963/
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 Join Subquery with *
提问by MAZUMA
I'm putting together a fairly simple query with a subquery in the JOIN statement. It only works if I include an * in the subquery select. Why?
我正在将一个相当简单的查询与 JOIN 语句中的子查询放在一起。仅当我在子查询选择中包含 * 时才有效。为什么？
This works
这有效
$sql = 'SELECT locations.id, title, name, hours.lobby
FROM locations
LEFT JOIN states ON states.id = locations.state_id
LEFT JOIN (SELECT *, type_id IS NOT NULL AS lobby FROM location_hours) AS hours ON locations.id = hours.location_id
GROUP BY locations.id';
This doesn't
这不
$sql = 'SELECT locations.id, title, name, hours.lobby
FROM locations
LEFT JOIN states ON states.id = locations.state_id
LEFT JOIN (SELECT type_id IS NOT NULL AS lobby FROM location_hours) AS hours ON locations.id = hours.location_id
GROUP BY locations.id';
Should I even be doing it this way? I thought * was not best if you don't need all the fields?
我应该这样做吗？如果您不需要所有字段，我认为 * 不是最好的？
回答by Ashalynd
try this (if I understood your intent correctly, that you wanted to filter on type_id not null):
试试这个（如果我正确理解你的意图，你想过滤 type_id 不为空）：
$sql = 'SELECT locations.id, title, name, hours.lobby
FROM locations
LEFT JOIN states ON states.id = locations.state_id
LEFT JOIN (SELECT location_id, type_id AS lobby FROM location_hours
WHERE type_id IS NOT NULL) AS hours ON locations.id = hours.location_id
GROUP BY locations.id';
The explanation is that you have to select in the inner query all the fields which are referenced in the outer query.
解释是您必须在内部查询中选择外部查询中引用的所有字段。
回答by Chill
Since you want to have non-NULL values for type_id, you shouldn't be using a LEFT JOIN for it at all. You'll want the following, which uses a standard JOIN.
由于您希望 type_id 具有非 NULL 值，因此根本不应该使用 LEFT JOIN。您将需要以下使用标准 JOIN 的内容。
$sql = 'SELECT locations.id, title, name, location_hours.type_id
FROM locations
JOIN location_hours ON location_hours.location_id = locations.id
LEFT JOIN states ON states.id = locations.state_id
WHERE location_hours.type_id IS NOT NULL
GROUP BY locations.id';
The whole point of a JOIN is that it only joins rows that actually exist. So you won't get any rows where location_hours doesn't have a corresponding location_id to locations.id. Then you just filter out NULL values for location_hours.type_id.
JOIN 的全部意义在于它只连接实际存在的行。因此，您不会得到 location_hours 没有对应 location_id 到locations.id 的任何行。然后您只需过滤掉 location_hours.type_id 的 NULL 值。
