Re: Equivalence of CROSS JOIN and comma
От | Adam Mackler |
---|---|
Тема | Re: Equivalence of CROSS JOIN and comma |
Дата | |
Msg-id | 20121016044604.GA49446@bk.macklerlaw.com обсуждение исходный текст |
Ответ на | Re: Equivalence of CROSS JOIN and comma (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-novice |
That makes perfect sense. Thank you for clearing that up. As an aside, it doesn't seem that section 7.2.1.1 of the manual explains what you just said. I don't know whether or not it's in another section, but 7.2.1.1 is where I looked since it's the only reference in the index under "cross join" and there's no index entry for comma, either spelt out or as a symbol, including among the entries under "join." I don't think it would be inappropriate to mention the difference in binding precedence as a qualification of the cross-join-comma equivalence statement that I quoted in my original post. Also, now knowing what you just explained, I looked again at the manual and came across section 14.3, which, if not directly on point, does seems very closely related. In particular the fourth highlighted example (looking at the HTML version) strongly implies the same equivalence by being preceded with the words "...these three queries are logically equivalent:" and including three queries, two of which are the same as each other but for cross-joins versus commas. While it seems less likely that someone confused at the behavior that I was asking about, and who doesn't suspect it's a matter of binding precedence will find their way to section 14.3 as to 7.2.1.1, it does seem highly probably that someone who is reading section 14.3 for whatever reason would be of the mind to want to know about this distinction between the binding of cross-joins and commas. It might not be inappropriate to mention it there in section 14.3 as well as in section 7.2.1.1. Than you again for your prompt and effective response to my question. Adam Mackler On Mon, Oct 15, 2012 at 09:52:37AM -0400, Tom Lane wrote: > Adam Mackler <pgsql-novice@mackler.org> writes: > > The PostgreSQL manual [1] reads in part: > > "FROM T1 CROSS JOIN T2 is equivalent to FROM T1, T2." > > That's true as far as it goes ... > > > Yet here are two statements that are identical to each other except > > that one has a CROSS JOIN where the other has a comma: > > > WITH t1 (val) AS ( VALUES (1) ), > > t2 (name) AS ( VALUES ('foo') ), > > t3 (num) AS ( VALUES (1) ) > > SELECT * FROM t1 CROSS JOIN t2 JOIN t3 ON num=val; > > > WITH t1 (val) AS ( VALUES (1) ), > > t2 (name) AS ( VALUES ('foo') ), > > t3 (num) AS ( VALUES (1) ) > > SELECT * FROM t1, t2 JOIN t3 ON num=val; > > > and one works but the other doesn't. > > The issue there is that JOIN binds tighter than comma. The first one > means > > ((t1 CROSS JOIN t2) JOIN t3 ON num=val) > > and the second one means > > t1, (t2 JOIN t3 ON num=val) > > which is equivalent to > > (t1 CROSS JOIN (t2 JOIN t3 ON num=val)) > > so the reference to t1.val fails because t1 isn't part of the JOIN that > the ON condition is attached to. > > People migrating from MySQL tend to get this wrong because MySQL gets it > wrong :-(, or at least it did in older versions --- I've not checked it > lately. The SQL standard is perfectly clear about this though. > > regards, tom lane -- Adam Mackler
В списке pgsql-novice по дате отправления: