MySQL 仅当存在于另一个表中时才从表中选择值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30158126/
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
Select value from table only if exist in another
提问by Tompo
I have two tables.
我有两张桌子。
Table one:
表一：
ID COLOR
1 white
2 red
3 black
4 blue
5 yellow
Table two:
表二：
ID COLOR
1 white
2 white
3 red
4 black
Output should be:
输出应该是：
1 white
2 red
3 black
(exclude 2 values that don't exist in second table - blue and yellow + exclude second white).
（排除第二个表中不存在的 2 个值 - 蓝色和黄色 + 排除第二个白色）。
I tried different JOIN and EXIST queries, no luck. Thanks.
我尝试了不同的 JOIN 和 EXIST 查询，但没有运气。谢谢。
回答by pala_
where existsis appropriate for this.
where exists是合适的。
select *
from t1
where exists
(select 1
from t2 where color = t1.color);
The subquery is a correlated subquery (as it refers to a value from the other query), and as such it is executed for every row of the outer query. So all the inner query needs to do, is check and see if the colour from the outer query (and first table) is present in the second table.
子查询是一个相关子查询（因为它引用来自另一个查询的值），因此它对外部查询的每一行执行。所以所有内部查询需要做的就是检查外部查询（和第一个表）的颜色是否存在于第二个表中。
回答by Devon
SELECT DISTINCT t1.* FROM t1
INNER JOIN t2 ON t1.color = t2.color;
Just another way to get the same thing as @pala_
获得与@pala_ 相同的东西的另一种方式
