MySQL INNER JOIN 之前的 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10133356/
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
WHERE clause before INNER JOIN
提问by user1124535
If I have
如果我有
SELECT * FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.id = t2.id
WHERE t1.user='bob';
Does the WHEREclause run after the two tables are JOINED?
该WHERE子句是否在两个表之后运行JOINED？
How do I make it so it runs prior to the JOIN?
我如何使它在 JOIN 之前运行？
回答by Mosty Mostacho
The whereclause will be executed before the joinso that it doesn't join unnecessary records. So your code is fine the way it is.
该where子句将在 之前执行，join以便它不会加入不必要的记录。所以你的代码很好。
回答by hkf
Change the WHEREto another JOINcondition
更改WHERE为另一个JOIN条件
LEFT JOIN Table2 t2 on t1.id = t2.id AND t1.user='bob'
LEFT JOIN Table2 t2 on t1.id = t2.id AND t1.user='bob'
回答by tihe
In my experience in a left join you cannot exclude records in the 'left' (t1) table in the ON-statement since - by definition - all t1 records will be included. The where statement does work as it will be applied to the result of the join afterwards.
根据我在左连接中的经验，您不能在 ON 语句中排除“左”(t1) 表中的记录，因为 - 根据定义 - 所有 t1 记录都将被包括在内。where 语句确实有效，因为它将在之后应用于连接的结果。
I do not exactly know what you want to achieve but most probably an inner join suits your needs as well and then you canadd the t1.user='bob' condition to the ON-statement.
我不完全知道您想要实现什么，但很可能内部连接也适合您的需求，然后您可以将 t1.user='bob' 条件添加到 ON 语句中。
But if Mosty Mostacho is correct, the location (WHERE vs ON) of the condition is not relevant for speed of execution.
但是，如果 Mosty Mostacho 是正确的，则条件的位置（WHERE 与 ON）与执行速度无关。
回答by user2782001
RIGHT JOIN was the solution:
RIGHT JOIN 是解决方案：
SELECT cars.manufacturer, cars.year FROM cars
RIGHT JOIN (SELECT m.manufacturer FROM cars AS m ORDER BY m.year DESC LIMIT 3) subq
ON cars.manufacturer=subq.manufacturer
Haven't put it through the full rigors yet, but seems to work.
还没有完全通过严格的测试，但似乎有效。
回答by Ivan Olshansky
You should just add t1.user='bob'condition to ONclause before other condition and it will be evaluated first:
您应该在其他条件之前t1.user='bob'向ON子句添加条件，它将首先被评估：
SELECT * FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.user='bob' AND t1.id = t2.id;
回答by Georgi Mirchev
What you may use is table expression after FROM like this:
您可以使用的是 FROM 之后的表表达式，如下所示：
SELECT *
FROM (SELECT
id
FROM Table1
WHERE user = 'bob') AS t1
LEFT JOIN Table2 t2
ON t1.id = t2.id
回答by g3rv4
you can do
你可以做
SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.id=t2.id AND t1.user='bob';
