Joining 2 tables with priority

[sam93]

Hi,

I have to join data from 2 tables, say A and B, so that data from A takes priority over B. e.g. A has data
CD NAME
1 a
2 b
and B has
CD NAME
2 bb
3 cc

The result should be:
1 a
2 b
3 cc

I am doing it as follows:
SELECT DECODE(A.CD,NULL,B.CD,A.CD) CD, DECODE(A.CD,NULL,A.NAME,B.NAME) NAME
FROM A FULL OUTER JOIN B ON A.CD = B.CD

and it works fine. But if I have say 100 columns in each table, I will be doing 100 decodes in the statement. I am not sure if this is the best way to do it.

The other way I can think of is as follows:
SELECT A.CD, A.NAME FROM A
UNION ALL
(SELECT B.CD, B.NAME FROM B MINUS SELECT A.CD, A.NAME FROM A)

But this way I will be accessing table A twice.

Can you please suggest what's the best way to do it? I am using Oracle.

Thanks.

 

[SantaMufasa]

First of all, accessing a table "twice" is not as bad as it might seem: The data are cached in memory (in the Database Buffer Cache), so it is very quick.

Second, you should never be afraid of performance using Oracle set operators (UNION, UNION ALL, MINUS, INTERSECT). They are the tightest, quickest running code of any in the Oracle tool box.

Third,

I am doing it as follows...and it works fine.

I don't think so...Neither of your code sets does what you want:

SELECT DECODE(A.CD,NULL,B.CD,A.CD) CD, DECODE(A.CD,NULL,A.NAME,B.NAME) NAME
FROM A FULL OUTER JOIN B ON A.CD = B.CD;

CD NAME
-- ----
 2 bb
 1
 3

SELECT A.CD, A.NAME FROM A
UNION ALL
(SELECT B.CD, B.NAME FROM B MINUS SELECT A.CD, A.NAME FROM A);

CD NAME
-- ----
 1 a
 2 b
 2 bb
 3 cc

Here is tight, quick code that actually does what you want:

select * from a
union
select * from b
 where not exists (select 'x' from a
                    where a.cd = b.cd);

CD NAME
-- ----
 1 a
 2 b
 3 cc

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[sam93]

Thanks SM. The 1st query had a typo.
SELECT DECODE(A.CD,NULL,B.CD,A.CD) CD, DECODE(A.CD,NULL,B.NAME,A.NAME) NAME
FROM A FULL OUTER JOIN B ON A.CD = B.CD;
would have worked. The 2nd one is definitely wrong. Any way, your answer addresses what I was looking for. Thanks for your help.

 

[Welshbird]

sam93 - I'd consider giving Santa a star for that help.

Thanks,

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]