adding flag for common rows between two tables

Question

i have two tables say A and B. B is a subset of A. what i want to do is this : Add a flag column to table A(only for viewing, not permanently in the table) and the value of this flag should be yes for common rows between A and B and no for non common rows. For ex:

A table
Column1 Column2 Column3
X1      X2      X3
Y1      Y2      Y3
Z1      Z2      Z3

---

select * from A where column1=Y1; to get B

now my final output should be

Column1 Column2 Column3 FLAG
X1      X2      X3      NO   
Y1      Y2      Y3      YES 
Z1      Z2      Z3      NO

---

i have to everything below the code block in 1 sql statement(extracting B and adding flag). i am just able to extract B. unable to add flag

Using oracle 11.2.0.2.0,sqlplus

Answer 1

Use an outer join to conditionally link tables A and B, then use a CASE() statement to test whether a given row in A matches a row in B.

select a.*
       , case when b.column1 is not null then 'YES' else 'NO' end as flag
from a left outer join b
        on a.column1 = b.column1

Note that this only works properly when there is just 0 or 1 instances of B.COLUMN1. If B contains multiple instances of any value of COLUMN1 then you can use this variant:

select a.*
       , case when b.column1 is not null then 'YES' else 'NO' end as flag
from a left outer join ( select distinct column1 from b ) b
        on a.column1 = b.column1