Select a value from the table only if it exists in another

Question

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 

The conclusion should be:

 1 white 2 red 3 black 

(exclude 2 values ​​that do not exist in the second table - blue and yellow + exclude the second white).

I tried different JOIN and EXIST queries, no luck. Thanks.

Answer 1

where exists is suitable for this.

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

demo here

A subquery is a correlated subquery (since it refers to a value from another query), and as such it is executed for each row of the external query. Thus, it is necessary to fulfill all internal queries, check and see if the color from the external query (and the first table) is present in the second table.

Answer 2

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

Another way to get the same as @pala _