How to create fast database queries

Archive for April 14th, 2009

Missing entries

Comments enabled. I *really* need your comment

From Stack Overflow:

I have a case where I want to choose any database entry that have an invalid Country, Region, or Area ID.

By invalid, I mean an ID for a country or region or area that no longer exists in my tables.

I have four tables: Properties, Countries, Regions, Areas.

I was thinking to do it like this:

FROM    Properties
        SELECT  CountryID
        FROM    Countries
        RegionID NOT IN
        SELECT  RegionID
        FROM    Regions
        AreaID NOT IN
        SELECT  AreaID
        FROM    Areas

Now, is my query right?

We have two options here:

  1. Use a LEFT OUTER JOIN with the three dimension tables (Countries, Regions and Areas), filtering out non-NULL values
  2. Use a NOT IN / NOT EXISTS approach shown above

LEFT JOIN intuitively seems to be more elegant and more efficient than subqueries. But is it really?
Read the rest of this entry »

Written by Quassnoi

April 14th, 2009 at 11:00 pm

Posted in SQL Server