-
Compare/filter 2 tables...help!!!
Would appreciate if someone could help!
I have 2 tables , table 1 and table 2. Exact copies of some records from table 1 also exist in table 2. What i need to do is display records that exist in table 1 but do not exist in table 2.
Thanks for your help!
-
Hi
SELECT * FROM table1
MINUS
SELECT * FROM table2
This query will return all the rows which are in table1 but not in table2.
The two tables need to have the same structure (columns, data types and so on), if not, than you may replace the "*" with the list of columns common to both tables.
Hope this helps.
Regards,
Georg
-
create table x1 (id int, name varchar(100))
create table x2 (id int, name varchar(100))
insert into x1 select 1,'a'
insert into x1 select 2,'b'
insert into x1 select 3,'c'
insert into x1 select 4,'c'
insert into x1 select 5,'c'
insert into x2 select 1,'a'
insert into x2 select 2,'b'
insert into x2 select 3,'c'
select min(TableName), id,name from
(
select 'Table1' as TableName, id,name
from x1
union all
select 'Table2' as TableName, id,name
from x2
) as mytable
group by id,name
having count(*) = 1
-
Hi MAK,
your code has a bug. It will return the correct rows ONLY if the second table X2 doesn't contain rows which aren't in X1 (so only if X2 is a subset of X1). As soon as X2 contains rows which are not in X1 it will also return this rows which is not wat olu wanted.
Imho it's also "too complicated" for the task at hand and probably wouldn't perform very well on big tables (first a UNION, like in my example, and then a GROUP BY on it, which is very sort intensive, the dabase will do it on the hard disks if there is not enough memory for the sort).
Regards,
Georg
-
a cheaper solution to the request and the one which usually performs the best is:
create table x1 (id int, name varchar(100))
create table x2 (id int, name varchar(100))
insert into x1 select 1,'a'
insert into x1 select 2,'b'
insert into x1 select 3,'c'
insert into x2 select 1,'a'
insert into x2 select 2,'b'
insert into x2 select 3,'c'
insert into x2 select 4,'d'
insert into x2 select 5,'e'
-- the same result acheived in a cheaper way
select x2.*
from x1
right join x2 on x1.id = x2.id -- join on whatever the PK is
where x1.id is null
just compare the execution plan with the proposal above ... then use whatever performs better in your case
-
Hi TSideov,
you are right it may perform better. I work mostly on datawarehouses and made the experience that select minus select for really big tables faster than an outer join is.
The other problem with data warehouses is that you have to load, cleanse and process external data and don't always have a primary key at the beginning of the process, but still have to get the delta to some of the datawarehouse tables.
Regards,
Georg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|