CODEWITHSUNDEEP
sqloracle-database
user1356163
user1356163

Reputation: 407

adding flag for common rows between two tables

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

Upvotes: 2

Views: 12777

Answers (3)

APC
APC

Reputation: 146179

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

Upvotes: 9

OraNob
OraNob

Reputation: 694

SELECT A.*, 'NO' 
FROM A
WHERE NOT EXISTS 
(SELECT 1 FROM B
WHERE B.COL1 = A.COL1
AND B.COL2 = A.COL2
AND B.COL3 = A.COL3) -- gets records only in A
UNION ALL 
(SELECT B.*, 'YES')  -- gets B records which are a subset of A

Since B is a subset of A - you already know these records should be tagged with a YES for your aliased column. The classical way of removing records from one recordset where they exist or don't exist in another recordset is of course using the EXISTS clause. The advantage of the EXISTS clause is it is a boolean operator and returns TRUE or FALSE to the call. And this return happens without the need for a full scan of the table - it is therefore faster (generally). You could also choose to use a MINUS clause, it might be more efficient. Try turning on the EXPLAIN PLAN.

Upvotes: 2

zmbq
zmbq

Reputation: 39013

You could try something like this:

SELECT A.*, 
       CASE WHEN EXISTS 
            (SELECT Column1 FROM B WHERE Column1=A.Column1) 
       THEN "YES" 
       ELSE "NO"
       END
FROM A

My PL-SQL is a bit rusty, example taken from here

You can also do a LEFT JOIN on B, and see if B.Column1 is NULL or not.

Upvotes: 2

Related Questions