This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Re: [HACKERS] Enhancing PGSQL to be compatible with Informix From: Don Baccus <dhogaza@pacifier.com> Date: Fri, 07 Jan 2000 13:35:52 -0800 At 12:00 AM 1/7/00 -0400, The Hermit Hacker wrote: >On Thu, 6 Jan 2000, Bruce Momjian wrote: > >> > select ... >> > from t1 inner join t4 on t1.x=t4.x, >> > t2 left outer join t1 >> > on t2.y=t1.y and >> > (t1.start_date between t2.start_date and t1.start_date), >> > t3 left outer join t1 on t3.x=t1.x and t3.y = t1.y; >> >> Let's be honest, folks. This is almost unreadable. I think we will >> need some simpler way to access _outer_ in addition to the ANSI way. Well...it took a minute to digest the Oracle version, too. Most joins are far simpler than the example. >How do the "books" talk about JOINs? What is the semi-standard syntax >that is generally used in samples? "SQL for smarties" gives examples of vendor-specific syntax then talks about outer joins more abstractly. It also points out that the existing vendor solutions have weaknesses. === Subject: Re: [HACKERS] Enhancing PGSQL to be compatible with Informix From: Don Baccus <dhogaza@pacifier.com> Date: Fri, 07 Jan 2000 13:45:58 -0800 At 06:56 AM 1/7/00 +0000, Thomas Lockhart wrote: >Hmm. I'm not sure what the Oracle example actually gives as a result, >and I find the syntax as confusing as others find SQL92 syntax ;) Me too :) As I pointed out in an earlier message, fortunately most of the outer join examples I've seen are simpler, and more readable in either style. Thanks, BTW, for the status update, it's about what I gathered from looking at the code. >Once two tables are mentioned in an "outer join", then individual >columns can no longer be qualified by the original table names. >Instead, you are allowed to put table and column aliases on the join >expression: > >select a, b, c, z > from (t1 left join t2 using (x)) as j1 (a, b, c) > right join t3 on (j1.a = t3.y); > >(I think I have this right; I'm doing it from memory and have been >away from it for a little while). Yeah, I think this is right, I'd seen in the syntax where a general table reference can be a join and hadn't thought about being able to table alias the entire result. This is useful, actually. Without the column aliases something like: select j1.a, j1.b, j2.foo ... makes it clear as to which join a column comes from. This clarity's often lacking in the Oracle-style queries, as I've noticed when I decipher them during my port-to-Postgres work. You need to unwind what comes from where, and often have to look at the data model to figure it out if the names are unique to the different tables and not fully qualified as "table_name.column_name". ===