NOT EXISTS 2

[dbomrrsm]

I have two tables in different databases that are exactly the same in structure.

I have added an additional column called test to tableA.

When I run the below code the NOT IN finds the difference between the tables (i.e. the additional column called test) but the NOT EXISTS doesn't - can anyone tell me why ?

select *
From 	dev.Information_Schema.Columns
Where	Table_Name = 'tableA'
and column_name not in
(select column_name
From 	smart.Information_Schema.Columns
Where	Table_Name = 'tableA')

select *
From 	dev.Information_Schema.Columns
Where	Table_Name = 'tableA'
and not exists
(select *
From 	smart.Information_Schema.Columns
Where	Table_Name = 'tableA')

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[gmmastros]

Exists simply checks for the presence of a record. As soon as it finds 1 record, it returns true. Under the right circumstances, Exists is very efficient, but it is not appropriate for this query.

select *
From     dev.Information_Schema.Columns
Where    Table_Name = 'tableA'
and not exists
([blue]select *
From     smart.Information_Schema.Columns
Where    Table_Name = 'tableA'[/blue])

So... the part in blue would return all the schema information for the existing columns in the tableA. Since there is at least 1 field already in the table, the query would return at least 1 row, so exists = true. You're using not exists, so False, and therefore no records are returned.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dbomrrsm]

George

Thanks for the clarification - very well explained.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[SQLDenis]

your not exists should be coded like this (untested)

select *
From     dev.Information_Schema.Columns d
Where    d.Table_Name = 'tableA'
and not exists
(select *
From     smart.Information_Schema.Columns s
Where    s.Table_Name = 'tableA'
and s.column_name = d.column_name)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[dbomrrsm]

SQLDenis

Excellent - thats got it - works a treat.

What I wanted to use this for was to see if there were any differences between the structure of one table and another.

To do this I would presumably need to place other and conditions in the not exists select to check for differences in other columns of the information_schema.columns.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]