CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Gary
Gary

Reputation: 4725

rewrite a union query

I have two tables which have the same structure, let’s say TA and TB. Most records in TA and TB are the same. There are some records in TA not in TB and some in TB are not in TA. I just want get all the records from TA and TB without duplicity:

Select * from TA
Union
Select * from TB

This query does give me the results I want. But the performance is not good as in production, there are more than half million data in both tables. Is there a simple way to get all the records from both table? Both tables have an id column which has unique value and can be joined by.

Upvotes: 1

Views: 1091

Answers (4)

Hart CO
Hart CO

Reputation: 34774

Try:

SELECT COALESCE(A.ID,B.ID), COALESCE(A.field2,B.field2), etc.
FROM A
FULL JOIN B
 ON A.Id = B.ID

Upvotes: 2

chue x
chue x

Reputation: 18803

You could use a NOT EXISTS + UNION ALL:

Select * from TA
UNION ALL
Select * from TB where not exists (select * from TA where TA.KEY_ID = TB.KEY_ID)

This gets you all data in TA and non-duplicates from TB.

Upvotes: 2

Stoleg
Stoleg

Reputation: 9300

UNION is quite slow when it comes to excluding duplicates. If you have a key on these tables try adding a WHERE clause to 2nd table to exclude duplicates. You would do it more intellegently, than db.

Upvotes: 0

Tom Studee
Tom Studee

Reputation: 10452

The only way to make this faster is to use UNION ALL which as you said there are duplicates, is problematic.

UNION is extremely efficient, optimized, and simple. There is no better way, unfortunately.

Upvotes: 0

Related Questions