SQL query to find where no primary key exists

Question

Hi I have re-added a foreign key relationship to a table in order to do so I had to abandon checking data on creation. I presume some parent(Company) objects have been deleted and I want to find the orphaned(Division) records. How would I find an row where the foreign key does not exist in the primary table?

This is what I was thinking but am struggling with the where clause.

               SELECT  tb_Division.DivisionName, 
                           tb_Division.DivisionCompanyID
                 FROM  tb_Division 
LEFT OUTER JOIN tb_Company ON tb_Division.DivisionCompanyID = tb_Company.CompanyID 
               WHERE (tb_Company.CompanyID = NULL 
                           OR 'doesn't exist in tb_Company')

Any pointers much appreciated.

Answer 1

You've almost got it, but you need to compare using the IS NULL predicate:

SELECT  d.DivisionName, d.DivisionCompanyID
FROM  tb_Division d LEFT OUTER JOIN tb_Company c 
  ON d.DivisionCompanyID = c.CompanyID 
WHERE c.CompanyID IS NULL

Or you could write it this way, which would do the same thing and maybe it's more intuitive:

SELECT  d.DivisionName, d.DivisionCompanyID
FROM  tb_Division d
WHERE NOT EXISTS (SELECT * FROM tb_Company c WHERE d.DivisionCompanyID = c.CompanyID);

Answer 2

Hi, you can also run this t-sql query against a SQL Server 2005/2008 to get all tables in the system not having a clustered or non-clustered index.

select name 
from sys.tables t 
where not exists(select * from sys.indexes i 
    where i.object_id = t.object_id and index_id > 0)
order by name

This tables are called a HEAP and may be significantly slower than tables with a clustered index.