Select value from table only if exist 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 

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).

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

Answer 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.