Re: RFE: Use JOIN instead of WHERE in Graphical Query Builder

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 />