• 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