只有在另一个表中存在时才从表中选择值

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查询,没有运气。谢谢。

2 个解决方案

#1

where exists is appropriate for this.

存在的地方适合这个。

select * 
  from t1
  where exists 
    (select 1
      from t2 where color = t1.color);

demo here

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.

子查询是相关子查询(因为它引用来自另一个查询的值),因此它对外部查询的每一行执行。所以内部查询需要做的就是检查并查看外部查询(和第一个表)中的颜色是否存在于第二个表中。

#2

SELECT DISTINCT t1.* FROM t1 
INNER JOIN t2 ON t1.color = t2.color;

Just another way to get the same thing as @pala_

只是另一种方式来获得与@pala_相同的东西

#1