Strange query gives strange results

Question

Strange query gives strange results

odepriester

SSC Enthusiast

Points: 184
More actions
September 6, 2013 at 4:28 am

#277823

Hi,
I found a view using a "bad practices example" query and I can't understand the results it gives.
The model is as following :
- an object has contacts (1..+inf).
- an object has a unique classification_code
- a contact may have addresses (0..+inf)
- an object may have descriptions (0..+inf)
- a description has a type
The query aims to retrieve all the objects with their address and a description of a given type (ie : 1234).
Here's the current query :
select o.id_object, o.type, ca.address1, ca.address2, d.description
from contacts c
left join contacts_addresses ca on c.id_contact = ca.id_contact
join objects o on o.id_object = c.id_object
left join descriptions d on d.id_object = c.id_object and d.type = '1234' and o.type = 'abcd'
My problem stands in the last criteria (bold) : I expected this query to retrieve only the objects of type "abcd" insteads of all the objects.
But it doesn't affect the result. Either I put it or not, I get all the objects.
I don't know why descriptions has been joined on contacts and not objects but I don't think it's important. And, of course, I also don't know why this last criteria is here !
Is anyone able to explain this ?:alien:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The filter is on the join and it's an outer join. So the filter is done before the join then the left join joins in the matching rows, while also returning any rows that don't match (definition of a left join)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

It's a nice little setup for augmenting Gail's fine explanation with some tables. Is id_object the PK of the object table, by any chance?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 3

As Gail stated, your "and o.type = 'abcd'" is part of the join predicate for the left join.

if you only want abcd typed rows in your result, you should move that criterium to the where clause of the query.

where o.type = 'abcd'

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

odepriester SSC Enthusiast Points: 184 More actions · Answer 4

Thanks for answers but the fact is that I don't need this "o.type = 'abcd'" and if I had done this query by myself I would never have written this part at all ! 🙂

I want to get all the objects (and that's what the query returns actually) but I wondered how the SQL Engine uses this criteria as the table in the criteria isn't the one concerned by the "left join".

In a vegetable way, it's like checking if a carrot matches another carrot and someone come and say "Hey, among your carrots, only consider the red tomatoes". Well ...nonsense ...:crazy:

I already know the difference between filtering in the "left join" part and in the "where" part

So I thought that

SELECT 1

FROM a

INNER JOIN b ON a.id_a = b.id_a

LEFT JOIN c ON c.id_b = b.id_b AND a.version = 12

would have been used in the same way as

SELECT 1

FROM a

INNER JOIN b ON a.id_a = b.id_a AND a.version = 12

LEFT JOIN c ON c.id_b = b.id_b

although my results seem to be the same as

SELECT 1

FROM a

INNER JOIN b ON a.id_a = b.id_a

LEFT JOIN c ON c.id_b = b.id_b

Btw, I don't have any idea of what I would expect by using the 1st syntax. 😉

@ChrisM@Work: Yes, object_id is the object table PK

odepriester SSC Enthusiast Points: 184 More actions · Answer 5

GilaMonster (9/6/2013)
The filter is on the join and it's an outer join. So the filter is done before the join then the left join joins in the matching rows, while also returning any rows that don't match (definition of a left join)

I thought about 3/4 times about what you said and I hadn't seen in my results that you were right.

The fields from the description table are NULL when the rows don't match o.type = 'abcd'.

Summary : I have just discovered a bug in the application :w00t:

Thx

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

odepriester (9/6/2013)
Thanks for answers but the fact is that I don't need this "o.type = 'abcd'" and if I had done this query by myself I would never have written this part at all ! 🙂
I want to get all the objects (and that's what the query returns actually) but I wondered how the SQL Engine uses this criteria as the table in the criteria isn't the one concerned by the "left join".
In a vegetable way, it's like checking if a carrot matches another carrot and someone come and say "Hey, among your carrots, only consider the red tomatoes". Well ...nonsense ...:crazy:
I already know the difference between filtering in the "left join" part and in the "where" part
So I thought that
SELECT 1
FROM a
INNER JOIN b ON a.id_a = b.id_a
LEFT JOIN c ON c.id_b = b.id_b AND a.version = 12
would have been used in the same way as
SELECT 1
FROM a
INNER JOIN b ON a.id_a = b.id_a AND a.version = 12
LEFT JOIN c ON c.id_b = b.id_b
although my results seem to be the same as
SELECT 1
FROM a
INNER JOIN b ON a.id_a = b.id_a
LEFT JOIN c ON c.id_b = b.id_b
Btw, I don't have any idea of what I would expect by using the 1st syntax. 😉
@ChrisM@Work: Yes, object_id is the object table PK

The first syntax: left-joined rows from table c would be eliminated from the output where a.version = 12.

I think this is where you are struggling - you say you 'know the difference between filtering in the "left join" part and in the "where" part' but your post indicates otherwise. If you have a filter in the join of a left-joined table, it will filter rows from that table, not from the table it's joined to. Duplicate rows may be eliminated from the table it's joined to if the relationship is one to many.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

odepriester (9/6/2013)
GilaMonster (9/6/2013)
The filter is on the join and it's an outer join. So the filter is done before the join then the left join joins in the matching rows, while also returning any rows that don't match (definition of a left join)
I thought about 3/4 times about what you said and I hadn't seen in my results that you were right.
The fields from the description table are NULL when the rows don't match o.type = 'abcd'.
Summary : I have just discovered a bug in the application :w00t:
Thx

You've discovered a small black hole in your understanding of how SQL joins work!

Jokes aside, the query is fairly straightforward and from your description, is behaving exactly as it should.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

odepriester SSC Enthusiast Points: 184 More actions · Answer 8

I'll wait a bit longer before passing certification 😉

I have never needed to filter on another table that the "directly" concerned one within a join. And so never wondered if it was possible or not.

But you're right : another of my knowledge holes is now filled 😀