Order of join operations: would these two FROM clauses produce the same results?

Question

Would these two FROM clauses produce the same results? And if not, is there a way to write the the first one so that no parenthesis are needed?

FROM            SALESTAX
     RIGHT JOIN (            ITEMS
                  RIGHT JOIN (            PINVOICE
                               INNER JOIN PINVDET ON PINVOICE.PNV_INVOICENO = PINVDET.PND_INVOICENO AND PINVOICE.PNV_Site = PINVDET.PND_Site
                             ) ON ITEMS.ITE_INVNO = PINVDET.PND_INVNO
                ) ON SALESTAX.STX_GroupID = PINVDET.PND_TAX1
      FULL JOIN (            CUSTMS
                  RIGHT JOIN CUSMER ON CUSTMS.TMS_CODE = CUSMER.CUS_TERM
                ) ON PINVDET.PND_CUSTID = CUSMER.CUS_CustID

FROM  CUSTMS RIGHT JOIN
      CUSMER ON TMS_CODE = CUS_TERM FULL JOIN
      PINVDET ON PND_CUSTID = CUS_CustID LEFT JOIN
      PINVOICE ON PNV_INVOICENO = PND_INVOICENO AND PNV_Site = PND_Site LEFT JOIN
      SALESTAX on STX_GROUPID = PND_TAX1 left join
      ITEMS on ITE_INVNO = PND_INVNO

EDIT: While I'd like to know the answer to the first question, I'm more immediately interested in just having a more straightforward version of the first FROM clause that doesn't need parenthesis so if you'd rather just rewrite it than compare the two then feel free to just do that.

@BrandonMoore my impression is that, in the comments, Aaron is encouraging you to try them and check the results you get. He noted that the ***plan*** might be the same which means you may get the same performance profile. However, without you actually going through and executing the queries and getting an actual execution plan, there's not really a cookie-cutter way for us to guide you into a "better" way. — swasheck, May 23 '13 at 20:31
In this case there probably is a cookie cutter way. Forget about the word right @BrandonMoore. It is doing you no good. — Zane, May 23 '13 at 20:35
@swasheck Okay, I thought that there would be a logical order of operations to the way the queries are interpreted and that someone who is a little better in this area than me might be able to tell whether these two queries are equivalent. It sounds like you're saying that there is no way to know, but that would also mean that you couldn't predict what results SQL is going to give which doesn't seem right to me. — Brandon Moore, May 23 '13 at 20:36
@BrandonMoore what i'm saying is that you should run the query and see if it gives the same results. then you can look at the plan to see if it got those results the same way. — swasheck, May 23 '13 at 20:38
@BrandonMoore whoooah! That is not what is being said. What's being said is if you look at your query plan and test it out you will be able to find this information yourself. What is also being said is you have on your hands a nightmare query and people are not going to want to sift through it to figure out an answer when we can point you in a direction to figure it out yourself. — Zane, May 23 '13 at 20:39

ypercubeᵀᴹ · Accepted Answer · 2013-05-23T21:20:22.630

14

I have no idea if the 1st is equivalent to the 2nd (first because the queries are unfriendly formatted, to say the least and second because RIGHT joins are kind of confusing, since many are used to write using LEFT joins.) But to answer the question:

Is there a way to write the the first one so that no parenthesis are needed?

Yes, you can simply remove the parentheses from the 1st query.

Keeping the parentheses and formatted with some white space:

FROM 
        SALESTAX
    RIGHT JOIN 
        (   ITEMS
        RIGHT JOIN 
            (   PINVOICE
            INNER JOIN 
                PINVDET 
              ON  PINVOICE.PNV_INVOICENO = PINVDET.PND_INVOICENO 
              AND PINVOICE.PNV_Site = PINVDET.PND_Site
            ) 
          ON ITEMS.ITE_INVNO = PINVDET.PND_INVNO
        ) 
      ON SALESTAX.STX_GroupID = PINVDET.PND_TAX1
    FULL JOIN 
        (   CUSTMS
        RIGHT JOIN
            CUSMER 
          ON CUSTMS.TMS_CODE = CUSMER.CUS_TERM
        ) 
      ON PINVDET.PND_CUSTID = CUSMER.CUS_CustID

Without parentheses and white-space formatted:

FROM    
        SALESTAX
    RIGHT JOIN
            ITEMS
        RIGHT JOIN
                PINVOICE
            INNER JOIN 
                PINVDET 
              ON  PINVOICE.PNV_INVOICENO = PINVDET.PND_INVOICENO
              AND PINVOICE.PNV_Site = PINVDET.PND_Site
          ON ITEMS.ITE_INVNO = PINVDET.PND_INVNO
      ON SALESTAX.STX_GroupID = PINVDET.PND_TAX1
    FULL JOIN
            CUSTMS
        RIGHT JOIN 
            CUSMER 
          ON CUSTMS.TMS_CODE = CUSMER.CUS_TERM
      ON PINVDET.PND_CUSTID = CUSMER.CUS_CustID

To answer the other question, about the 2nd query, no it isn't equivalent. You missed the table aliases and changed an inner join to left join. This is equivalent to the 1st:

FROM  CUSMER  
        LEFT JOIN
      CUSTMS     ON  CUSTMS.TMS_CODE = CUSMER.CUS_TERM
   FULL JOIN
      PINVDET
        INNER JOIN                                      -- this is changed
      PINVOICE   ON  PINVOICE.PNV_INVOICENO = PINVDET.PND_INVOICENO
                 AND PINVOICE.PNV_Site = PINVDET.PND_Site
        LEFT JOIN
      ITEMS      ON  ITEMS.ITE_INVNO = PINVDET.PND_INVNO
        LEFT JOIN
      SALESTAX   ON  SALESTAX.STX_GroupID = PINVDET.PND_TAX1
   ON PINVDET.PND_CUSTID = CUSMER.CUS_CustID

edited May 23 '13 at 21:20

answered May 23 '13 at 20:18

ypercubeᵀᴹ

113,259
19
174
235

1

. . You *can* remove the parentheses, but you change the meaning of the code. – Gordon Linoff May 23 '13 at 20:19
@GordonLinoff Do you really? – ypercubeᵀᴹ May 23 '13 at 20:20
1

-1, sorry. This is wrong. `t1 FULL JOIN (t2 RIGHT JOIN t3)` is different from `t1 FULL JOIN t2 RIGHT JOIN t3`. – ruakh May 23 '13 at 20:21
4

@ruakh Sorry but you are wrong. Try giving us an example with the `ON` conditions, too. Your example is missing them. – ypercubeᵀᴹ May 23 '13 at 20:26
Ok, this is still convoluted though. True, I didn't realize that you could just remove the parenthis... but I was thinking you'd have to rewrite it in a clearer manner when the parenthesis were gone. I'm wanting a clause that is easier to read very quickly. – Brandon Moore May 23 '13 at 20:40
1

@ypercube: This is a bit hard to do in the space of a comment, but . . . suppose `t1` contains the rows `1` and `2`, that `t2` contains the rows `1 A` and `3 C`, and that `t3` contains the rows `B` and `C`. Then `t1 FULL JOIN (t2 RIGHT JOIN t3 ON t2.ltr = t3.ltr) ON t1.nbr = t2.nbr` contains the rows `1 NULL NULL NULL`, `2 NULL NULL NULL`, and `NULL 3 C C`, whereas `t1 FULL JOIN t2 ON t1.nbr = t2.nbr RIGHT JOIN t3 ON t2.ltr = t3.ltr` contains only the row `NULL 3 C C`. (Note, BTW, that I corrected the placement of the `ON`-clause. Your version isn't even syntactically valid.) – ruakh May 23 '13 at 20:45
@ypercube . . . I get a syntax error when I do: `select * from INFORMATION_SCHEMA.COLUMNS c join INFORMATION_SCHEMA.COLUMNS c1 join INFORMATION_SCHEMA.COLUMNS c2 on c.table_name = c1.TABLE_NAME on c1.TABLE_NAME = c2.TABLE_NAME` – Gordon Linoff May 23 '13 at 20:49
1

@ruakh Your two samples have the `ON` conditions in different places (and that's why your second example is not valid). I did not move the `ON` conditions, I only removed parentheses (and that's why my example is valid.) – ypercubeᵀᴹ May 23 '13 at 20:51
3

@GordonLinoff See **[Fiddle](http://sqlfiddle.com/#!3/d41d8/14258)** (I changed the order of the two ON conditions) – ypercubeᵀᴹ May 23 '13 at 20:57
Oh, bizarre, SQL Server allows funky syntax that other DBMSes don't. Sorry, I should have checked. Well, I hardly recommend this approach, but I'll retract my downvote. – ruakh May 23 '13 at 21:01
6

@ruakh: **[Postgres](http://sqlfiddle.com/#!1/43f97/3)** allows it. **[Oracle](http://sqlfiddle.com/#!4/43f97/16)** allows it. It's funky (or not), it's standard SQL. – ypercubeᵀᴹ May 23 '13 at 21:06
1

@BrandonMoore I've edited the last query, it had a mistake before. the `FULL` join needs to be done after the inner joins, for the rewriting to be equivalent. And still, don't neglect to test. The complex conditions make it easy that I might have missed something. – ypercubeᵀᴹ May 23 '13 at 21:21
1

Am I only one that perfers the parens. `RIGHT JOIN ( INNER JOIN ON ) ON` is pretty rare. So I kinda like that it gets called out – Conrad Frix May 23 '13 at 21:28
3

@ConradFrix You and Access :) – ypercubeᵀᴹ May 23 '13 at 21:29
@ypercube Thanks for your help! FYI, I took the aliases out on purpose. Since they use 3 letter table prefixes at the beginning of all their fields I don't think they're really necessary here. – Brandon Moore May 23 '13 at 21:32
@ypercube boo no that's not what I meant. Access requires parens when there's three or more tables. I just don't often have ON that aren't near the JOIN. – Conrad Frix May 23 '13 at 21:36
@ConradFrix: I was joking (if that isn't obvious.) The question specifically asked how to remove parenthesis. – ypercubeᵀᴹ May 23 '13 at 21:37
I could barely hear myself amidst all that downvote-comment noise :) cc @ruakh – BoltClock May 24 '13 at 08:20

score 6 · Answer 2 · answered May 23 '13 at 20:19

I encourage you to write your from clauses by placing all left joins first followed by inner joins. This greatly simplifies trying to figure out what queries are doing. A series of left joins says "keep all the rows in the first table". A series of inner joins says "keep only rows where there are matches between tables." (Occasionally, you might need subqueries as in the first example.)

It is unlikely in this example that the two are the same. In the first, the full join is the "outermost" join. In the second, the full join is embedded in a series of joins. These are interpreted sequentially from the first to the last. One of these is probably converting the full join to an inner join or left join. Of course, the two could produce equivalent results if all the tables match.

The second example could probably be written as:

FROM  CUSMER  LEFT JOIN
      CUSTMS ON TMS_CODE = CUS_TERM LEFT JOIN
      PINVDET ON PND_CUSTID = CUS_CustID LEFT JOIN
      PINVOICE ON PNV_INVOICENO = PND_INVOICENO AND PNV_Site = PND_Site LEFT JOIN
      SALESTAX on STX_GROUPID = PND_TAX1 left join
      ITEMS on ITE_INVNO = PND_INVNO

(assuming that something after the full join is converting it to a left join anyway).

I agree, and that's how I write my own queries. Unfortunately, I need to make changes to the first query (which I did not write) and I'd like to rewrite it in a more coherent manner first. If the 2nd query is the same as the first, then I would accept this as the answer and use it. But if the second isn't the same as the first then having another query that's the same as the second wouldn't help me. — Brandon Moore, May 23 '13 at 20:33

Order of join operations: would these two FROM clauses produce the same results?

2 Answers2