CODEWITHSUNDEEP
sql-servert-sql
StephenGB
StephenGB

Reputation: 9

Simple query gone wrong

I must be having an off day. This should be obvious but I don't get it. 

-- check for necessary updates to dbnotes
select count(distinct table_name) 
from ccsv4.[INFORMATION_SCHEMA].[COLUMNS]

returns 46

select count(distinct table_name) 
from dbnotes

returns 44

select distinct table_name 
from ccsv4.[INFORMATION_SCHEMA].[COLUMNS]
where table_name not in (select distinct table_name from dbnotes)
order by table_name

returns nothing

select distinct table_name 
from dbnotes
where  table_name not in (select distinct table_name 
                          from ccsv4.[INFORMATION_SCHEMA].[COLUMNS])
order by table_name

returns nothing

What am I missing guys?

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You are using not in. If any value from the subquery is NULL, nothing will be returned.

With a subquery, always use not exists. It has the right semantics:

select distinct table_name
from ccsv4.[INFORMATION_SCHEMA].[COLUMNS] c
where not exists (select 1
                  from dbnotes d
                  where d.table_name = c.table_name 
                 );

I am pretty sure that tables have to have at least one column, so you might as well use information_schema.tables instead. It saves you the distinct:

select table_name
from ccsv4.information_schema.tables t
where not exists (select 1
                  from dbnotes d
                  where d.table_name = t.table_name 
                 );

Upvotes: 1

Related Questions