Friday, March 5, 2010

Outer join, please!

I was shown this piece of SQL gems:

delete from bad_table where name not in (select name from good_table);

Then something like "it has always fast until now". So I straighten this code to:

delete from bad_table where name in
(select b.name from bad_table b, good_table g
where b.name = g.name(+) and g.name is null)


The first query was fast but is low now because bad_table has grown since the code went into production. Yay! outer join saves the day!

No comments:

Post a Comment