January 24, 2013 at 2:37 pm
Hello all,
I am getting very strange behavior from a union all derived query being joined to a lookup table with a left join. The query is below but the names and faces have been changed to protect the innocent.
SELECT
A.ID,
B.ID,
A.*,
B.*
FROM
(SELECT * FROM table1 UNION ALL SELECT * FROM table2) A
LEFT JOIN B ON A.ID = B.ID
WHERE
B.ID IS NULL
Lets say the ID in table1 is 1 and the ID in table2 is 2. Table B has both ID 1 and 2 as it's rows. When I run that query I get back multiple rows with NULL values for B.ID that should not be there and when I run the query for just 1 table without the UNION ALL they don't appear.
As a note I can not replicate this in another database using the structure above so I believe that something is wrong in the link somewhere or something has happened that is not related to the data but for the life of me I can't figure out what. I have tried rebuilding indexes and recreating the view that contains the union all but neither did much good.
Any thoughts?
January 24, 2013 at 2:52 pm
I don't see your definition of table B.
January 24, 2013 at 3:01 pm
Table B is a lookup table that has multiple columns but contains the ID column from table1 and table2.
January 24, 2013 at 3:02 pm
Hack your query down to the minimum which still exhibits the strange behaviour you are observing. If you haven't figured it out by the end of the exercise, code up some sample data which still exhibits this behaviour, and post it here. Have a good look at the execution plans throughout, looking for differences.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 24, 2013 at 3:15 pm
Krys,
You might start with this and fill in something for table B:
declare @table1 table(
ID int
, critter_description varchar(50)
)
declare @table2 table(
ID int
, critter_description varchar(50)
)
insert into @table1(ID, critter_description)
values (1,'cow')
insert into @table2(ID, critter_description)
values (2,'dog')
SELECT A.ID,
B.ID,
A.*,
B.*
FROM
(
SELECT *
FROM @table1
UNION ALL
SELECT *
FROM @table2) A
LEFT JOIN B ON A.ID = B.ID
WHERE B.ID IS NULL
January 24, 2013 at 4:04 pm
Thanks all,
I seem to have figured it out. I did try recreating the issue in another database and could not reproduce the problem. It was only happening on this one table.
It turns out there was a bad ID in the table that made it passed the check constraints on the table. Someone/process must have disabled the check constraint and added the row as I couldn't insert into the table with it on. Once I removed that bad row the table stopped exhibiting the odd results.
Thanks again!
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply