union query only returns records from first source

[raindogs]

Hello all,

I have another puzzler here. I'm trying to do a union between two identical "occupants" tables in two databases (DB1 and DB2 in the example pasted below). Both have a primary key called "rowID", and I want all the records from the first source along with all of the records from the second whose rowID is not in the first source. I tried to set up a union query like so, but it only returns records from the first souce. For testing purposes, I have planetd some records in the second source with rowIDs that I *know* are unique, so this should be working. Any idea what I'm doing wrong?

SELECT 'source1' as 'source',* from DB1.dbo.occupants

UNION

SELECT 'source2' as 'source',* from DB2.dbo.occupants
where rowID NOT IN (
select rowID from DB1.dbo.occupants
)

ORDER BY source

Thanks a lot,
Alex

 

[SQLDenis]

you have a NULL value in that column

change
where rowID NOT IN (
select rowID from DB1.dbo.occupants
)


to
where rowID NOT IN (
select rowID from DB1.dbo.occupants where rowID IS NOT NULL
)

or better yet use NOT EXISTS


SELECT 'source2' as 'source',d2.* from DB2.dbo.occupants d2
where not exists (
select * from DB1.dbo.occupants where rowid = d2.rowid
)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

also if you don't care about distinct values change UNION to UNION ALL since it will perform faster

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/