sql - Matching two columns

Question

I have a two tables. TableA has two columns and TableB has one column.

TableA

ID | TERM_A

TableB

TERM_B

TERM_A and TERM_B column contain terms . I want to take intersect terms from TERM_A column of TableA and TERM_B column of TableB with sql on Oracle 10. My result table should be like that :

• Result_Table

  ID | INTERSECT_TERMS

How can I do that?

Answer 1

The INTERSECT operator returns a resultset containing the matching values from the two queries.

select * from tableA
where term_a in 
    ( select term_a from tableA
      intersect
      select term_b from tableB )
;

Because you want to select additional columns from TABLEA you need to use the output of the intersection as a sub-query.