Excluding rows 2

[asimeqi]

I have 2 tables, table_1 and table_2.
Table 1 has all the rows of table_2 and some more.
I need to find the rows in table 1 that are not in table 2.
All I could think of is to use a cursor. There has to be another way, right?

 

[checkai]

Yes there is another way! how do your tables link? with an ID field?

"...your mom goes to college..."

 

[asimeqi]

Yes, they have the same primary_key.

 

[ronspree]

How about:

SELECT * FROM Table1 WHERE SomeID NOT IN (SELECT SomeID FROM Table2) AS Table2IDs

 

[asimeqi]

That works but it is a bit slow. Table2 has 500,000 rows. Table1 has 20,000,000 rows. I could use all speed ups that I can get.

 

[ronspree]

Try this:

SELECT Table1.* FROM Table1 FULL OUTER JOIN Table2 ON Table1.SomeID = Table2.SomeID WHERE Table2.SomeID IS NULL

 

[ethorn10]

And still slightly faster might be:

SELECT a.*,
FROM Table1 a LEFT OUTER JOIN
     Table2 b on a.SomeID = b.SomeID
WHERE b.SomeID IS NULL

Not sure that the FULL OUTER JOIN is needed. And if you want speed improvements, a cursor is out of the question with that amount of data.

 

[ronspree]

Very nice, ethorn, thanks.

 

[vbkris]

i would also suggest table hints. it can quicken it up.

e.g:
SELECT a.*,
FROM Table1 WITH(NOLOCK) a LEFT OUTER JOIN
Table2 WITH(NOLOCK) b on a.SomeID = b.SomeID
WHERE b.SomeID IS NULL


Known is handfull, Unknown is worldfull

 

[asimeqi]

Thank you guys.
The LEFT OUTER JOIN did it.
For future readers of this thread I think it is worth mentioning that the join is way faster if the 2 tables are ordered by the join attribute. In that case the optimizer can perform a MERGE JOIN. I discovered this the hard way.