Strange query gives strange results

  • 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:

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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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 πŸ˜€

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

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