Thread: RFE: Use JOIN instead of WHERE in Graphical Query Builder
I'm using pgAdmin 1.14.3, and its Graphical Query Builder generates WHERE clauses for joins. It would be nice if it generated JOIN clauses instead. Thanks in advance, Daniel Serodio
Hi! On Tue, Jul 17, 2012 at 7:36 PM, Daniel Serodio (lists) <daniel.lists@mandic.com.br> wrote: > I'm using pgAdmin 1.14.3, and its Graphical Query Builder generates WHERE > clauses for joins. It would be nice if it generated JOIN clauses instead. Is there any kind of difference between WHERE or JOINs in PostgreSQL? Aren't they all a cartesian product internally? Best regards, Nelson
Nelson A. de Oliveira wrote: <blockquote cite="mid:CAARFvTVj8tmZn+SiYCqYodEwey1wpfKfY0RvEHsU1PfgPeCupA@mail.gmail.com" type="cite"><prewrap="">Hi! On Tue, Jul 17, 2012 at 7:36 PM, Daniel Serodio (lists) <a class="moz-txt-link-rfc2396E" href="mailto:daniel.lists@mandic.com.br"><daniel.lists@mandic.com.br></a> wrote: </pre><blockquote type="cite"><pre wrap="">I'm using pgAdmin 1.14.3, and its Graphical Query Builder generates WHERE clauses for joins. It would be nice if it generated JOIN clauses instead. </pre></blockquote><pre wrap=""> Is there any kind of difference between WHERE or JOINs in PostgreSQL? Aren't they all a cartesian product internally? </pre></blockquote> They may be implemented the same way in PostgresSQL, but <span>they're two different concepts. Becauseof this, I find it a lot easier to </span>understand a complex query when the joins are in a JOIN clause.<br /><br/> In more practical terms, it's easier to change "a JOIN b ON a.pk = b.fk" to "a LEFT JOIN b ON a.pk = b.fk" (justhave to type the LEFT keyword, regardless of which tables/columns are used) then change "WHERE a.pk = b.fk" to "WHEREa.pk = b.fk OR b.fk IS NULL" (have to add "<span>OR b.fk IS NULL", which changes according to which tables/columnsare used).<br /><br /> Regards,<br /> Daniel Serodio<br /><br /></span>
On Tue, 2012-07-17 at 20:06 -0300, Daniel Serodio (lists) wrote: > Nelson A. de Oliveira wrote: > > Hi! > > > > On Tue, Jul 17, 2012 at 7:36 PM, Daniel Serodio (lists) > > <daniel.lists@mandic.com.br> wrote: > >> I'm using pgAdmin 1.14.3, and its Graphical Query Builder generates WHERE > >> clauses for joins. It would be nice if it generated JOIN clauses instead. > > > > Is there any kind of difference between WHERE or JOINs in PostgreSQL? > > Aren't they all a cartesian product internally? > They may be implemented the same way in PostgresSQL, They are. > but they're two different concepts. How so? > Because of this, I find it a lot easier to > understand a complex query when the joins are in a JOIN clause. > I do agree here. > In more practical terms, it's easier to change "a JOIN b ON a.pk = b.fk" > to "a LEFT JOIN b ON a.pk = b.fk" (just have to type the LEFT keyword, > regardless of which tables/columns are used) then change "WHERE a.pk = > b.fk" to "WHERE a.pk = b.fk OR b.fk IS NULL" (have to add "OR b.fk IS > NULL", which changes according to which tables/columns are used). > Yes, and it's less risky. If you use a JOIN, you have to add a ON clause if you don't want to get a syntax error. That protects you from cartesian product. To get back on the request, that could be interesting to do, and probably not hard. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Guillaume Lelarge wrote: <blockquote cite="mid:1342644219.1989.89.camel@localhost.localdomain" type="cite"><pre wrap="">OnTue, 2012-07-17 at 20:06 -0300, Daniel Serodio (lists) wrote: </pre><blockquote type="cite"><pre wrap="">Nelson A. de Oliveira wrote: </pre><blockquote type="cite"><pre wrap="">Hi! On Tue, Jul 17, 2012 at 7:36 PM, Daniel Serodio (lists) <a class="moz-txt-link-rfc2396E" href="mailto:daniel.lists@mandic.com.br"><daniel.lists@mandic.com.br></a> wrote: </pre><blockquote type="cite"><pre wrap="">I'm using pgAdmin 1.14.3, and its Graphical Query Builder generates WHERE clauses for joins. It would be nice if it generated JOIN clauses instead. </pre></blockquote><pre wrap="">Is there any kind of difference between WHERE or JOINs in PostgreSQL? Aren't they all a cartesian product internally? </pre></blockquote><pre wrap="">They may be implemented the same way in PostgresSQL, </pre></blockquote><pre wrap=""> They are. </pre><blockquote type="cite"><pre wrap=""> but they're two different concepts. </pre></blockquote><pre wrap=""> How so?</pre></blockquote> My relational algebra is a little rusty, but from what I recall plus a quick Wikipedia read, WHEREis a "selection (σ)" while JOIN is a "join (⋈, θ, ⋉ or ▷"): <a class="moz-txt-link-freetext" href="http://en.wikipedia.org/wiki/Relational_algebra#Set_operators">http://en.wikipedia.org/wiki/Relational_algebra#Set_operators</a><br /><blockquotecite="mid:1342644219.1989.89.camel@localhost.localdomain" type="cite"><pre wrap=""> </pre><blockquote type="cite"><pre wrap=""> Because of this, I find it a lot easier to understand a complex query when the joins are in a JOIN clause. </pre></blockquote><pre wrap=""> I do agree here. </pre><blockquote type="cite"><pre wrap="">In more practical terms, it's easier to change "a JOIN b ON a.pk = b.fk" to "a LEFT JOIN b ON a.pk = b.fk" (just have to type the LEFT keyword, regardless of which tables/columns are used) then change "WHERE a.pk = b.fk" to "WHERE a.pk = b.fk OR b.fk IS NULL" (have to add "OR b.fk IS NULL", which changes according to which tables/columns are used). </pre></blockquote><pre wrap=""> Yes, and it's less risky. If you use a JOIN, you have to add a ON clause if you don't want to get a syntax error. That protects you from cartesian product. To get back on the request, that could be interesting to do, and probably not hard. </pre></blockquote> Thanks.<br /><br /> Regards,<br /> Daniel Serodio<br />