Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Strange query gives strange results


Strange query gives strange results

Author
Message
odepriester
odepriester
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 21
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47447 Visits: 44405
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
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
Exploring Recursive CTEs by Example Dwain Camps
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6984 Visits: 8839
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

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


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


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
odepriester
odepriester
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 21
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
odepriester
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 21
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
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
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9028 Visits: 19041
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
Exploring Recursive CTEs by Example Dwain Camps
odepriester
odepriester
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 21
I'll wait a bit longer before passing certification Wink

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 :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search