Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Strange query gives strange results Expand / Collapse
Author
Message
Posted Friday, September 6, 2013 4:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 2:31 AM
Points: 144, 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 ?
Post #1492147
Posted Friday, September 6, 2013 4:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #1492149
Posted Friday, September 6, 2013 5:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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
Post #1492170
Posted Friday, September 6, 2013 6:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:21 AM
Points: 6,743, Visits: 8,517
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

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1492185
Posted Friday, September 6, 2013 6:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 2:31 AM
Points: 144, 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 ...

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
Post #1492206
Posted Friday, September 6, 2013 7:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 2:31 AM
Points: 144, 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

Thx
Post #1492223
Posted Friday, September 6, 2013 7:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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 ...

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
Post #1492227
Posted Friday, September 6, 2013 7:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:43 AM
Points: 6,869, Visits: 14,178
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

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
Post #1492230
Posted Friday, September 6, 2013 8:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 9, 2013 2:31 AM
Points: 144, Visits: 21
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
Post #1492265
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse