asked on

compare 2 large tables

i have 2 tables, lets say:

table1(id bigint) and table2(id bigint)

table1 table2
1 3
2 76
23 89
45 90
76 92
90 98

so i want to select all rows on table1 that does not exist on table2, i have this in place:

SELECT * FROM TABLE1 AS T1
WHERE (CASE WHEN EXISTS(SELECT TOP 1 ID FROM TABLE2 WHERE ID = T1.ID) THEN 1 ELSE 0 END) = 0

this query takes a lot from the processor and too hard on my server, both of those tables have about 100000 rows. So i need another better solution to this.

Peter Chan

1. Make sure you have indices on ID for both tables
2. Try
SELECT * FROM TABLE1 AS T1
WHERE not EXISTS(SELECT TOP 1 ID FROM TABLE2 t2 WHERE t2.ID = T1.ID);
SELECT * FROM TABLE2 AS t2
WHERE not EXISTS(SELECT TOP 1 ID FROM TABLE1 t1 WHERE t1.ID = t2.ID);

ASKER CERTIFIED SOLUTION

mimran18

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

select
*
from table1
inner join (
            select ID
            from table1

            except

            select ID
            from table2
           ) cept on table1.id = cept.id

Open in new window

Asim Nazir

If you want to compare data of two tables then you may use Visual Studio's Data/Schema comparison options or even SqlDelta.

jitendra patil

as per your given example above,

i've tried to create a small example of except command which can get you near to the results.

DECLARE @Temp1 table (col1 int)
insert into @Temp1(col1)values(1)
insert into @Temp1(col1)values(2)
insert into @Temp1(col1)values(3)
insert into @Temp1(col1)values(4)
insert into @Temp1(col1)values(5)
insert into @Temp1(col1)values(6)

DECLARE @Temp2 table (col1 int)
insert into @Temp2(col1)values(1)
insert into @Temp2(col1)values(4)
insert into @Temp2(col1)values(6)
insert into @Temp2(col1)values(7)
insert into @Temp2(col1)values(4)
insert into @Temp2(col1)values(8)

select * from @Temp1
except
select * from @Temp2

please give a try & hope this helps.

Dale Fye

SELECT T1.*
FROM Table1 as T1
LEFT JOIN Table2 as T2
ON T1.ID = T2.ID
WHERE T2.ID IS NULL

rafaelrgl

ASKER

this way is really faster compare to the one i was using.