PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
Do you write joins like this?-VBForums
Results 1 to 4 of 4

Thread: Do you write joins like this?

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Do you write joins like this?

    An easy question for you on a Saturday (that is actually sunny and warm over here in New England!)

    I am working with someone else's query, and is this some kind of a shorthand to list a couple tables then do like a double join on them, like this?

    Code:
    FROM Emps 
    
    RIGHT OUTER JOIN Table_1 
    INNER JOIN Table_2 ON Table_1.Table_2_id = Table_2.Table_2_id ON dms1.dbo.Emps.p21ContactID = Table_2.salesrep_id 
    RIGHT OUTER JOIN Table_3 
    RIGHT OUTER JOIN Table_4 
    INNER JOIN address ON address.id = Table_4.address_id ON Table_3.contact_id = Table_4.id ON Table_1.contact_id = Table_4.id
    I have always written queries like this:
    Code:
    select * from Table_1 JOIN Table_2 on Table_2.col1 = Table_1.col1, etc
    one at a time, the table, the colums(s) joining on, the next table, etc.

    At first I was like is this person just throwing in a table without saying what to join on, then I saw the ON...ON. Is this a better way, worse way, or it all boils down to the same result and it's a matter of preference?

    Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,913

    Re: Do you write joins like this?

    I've never written or seen a query like that before but I do know that, once you throw in an outer join, all subsequent joins in the same query have to be outer joins too. If that code is working then, given that it has inner joins following outer joins, I can only assume that the placement of the ON keyword is a way to create subqueries. I started to try to rewrite that query using parenthesised subqueries and it soon got confusing enough that I decided that it was too much work. I have just woken up, after all. In short, if that works and is valid, I would have written it using subqueries and I think that that would be easier to read. That said, I be interested to see the execution plan for each and time the execution of each to see whether they work the same way in practice.

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room trying to forget the past
    Posts
    4,107

    Re: Do you write joins like this?

    Interesting. Thanks. I will take a look at it some more on Monday. The great outdoors is calling me right now, LOL.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,345

    Re: Do you write joins like this?

    I write queries like that but I make heavy use of parentheses and indentation. So for me that would look a bit like this:-
    SQL Code:
    1. SELECT *
    2. FROM TableA
    3. LEFT JOIN (TableB
    4.             JOIN TableC
    5.                 ON TableB.TableCID = TableC.ID)
    6.     ON TableA.TableBID = TableB.ID
    As JM guessed, it is basically to create sub-queries (though they're not technically sub queries) and I do it for two reasons:-

    1. To avoid cascading Outer Joins. As JM pointed out, if you put an Outer Join in a query, everything that hangs off it has to be an Outer Join too. But if you rearrange the order the ON clauses appear you can keep the joins that are logically INNERs as INNERs.
    2. To aid readability. E.g. In the above example, If Table B and C create some that feels like it should belong together, like a customers details including address, telephone etc., I can express that as a logical block in the query.

    You should note a few things:-
    The parentheses are unnecessary, they're purely there for clarity
    That first join doesn't have to be a left. It could be an inner e.g. when building up the logical blocks I mentioned in reason 2
    The Join from the outer query doesn't have to be to the first table in the inner query (i.e. TableA to TableB), it can be to anything in the inner query,(ie TableA to TableC).
    You can nest this type of sub query several layers deep.
    The inner query cannot reference the outer query because it's not in scope (so I couldn't have reference TableA in the inner queries join)

    To be honest, I rarely find the need to use this form when writing transactional systems where the queries tend to be quite simple. But I do a lot of work building data warehouses and this often involves pulling in data from 20 or 30 (or more) tables. This form can really help organise the code and it has the added advantage of keeping the query fairly "flat" (it doesn't have lots of ctes, corellated sub queries etc) which gives the optimiser a fair punt at consuming it efficiently.



    Edit>You should also note that this is the form most query builder UIs use. Try using the query builder in Access and you'll see what I mean. The reason is that it's easier to parse.
    Last edited by FunkyDexter; Jun 24th, 2019 at 02:23 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width