# Miscellaneous > SQL Scripts >  Compare/filter 2 tables...help!!!

## olu

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!

----------


## vgbreazu

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

----------


## MAK

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

----------


## vgbreazu

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

----------


## TSideov

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

----------


## vgbreazu

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

----------

