Change JOIN tutorial to focus more on explicit joins

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Change JOIN tutorial to focus more on explicit joins

PG Doc comments form
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/tutorial-join.html
Description:

The tutorial about joins makes the following statement about the explicit
JOIN operator:

> This syntax is not as commonly used as the one above

I think in 2020 this claim is no longer true, and I would love to see the
manual prefer the "modern" explicit JOIN operator rather than sticking to
the ancient implicit joins in the WHERE clause.
Reply | Threaded
Open this post in threaded view
|

Re: Change JOIN tutorial to focus more on explicit joins

Thomas Munro-5
On Thu, May 21, 2020 at 1:37 AM PG Doc comments form
<[hidden email]> wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/12/tutorial-join.html
> Description:
>
> The tutorial about joins makes the following statement about the explicit
> JOIN operator:
>
> > This syntax is not as commonly used as the one above
>
> I think in 2020 this claim is no longer true, and I would love to see the
> manual prefer the "modern" explicit JOIN operator rather than sticking to
> the ancient implicit joins in the WHERE clause.

+1

The "new" syntax is 28 years old, from SQL 92.  I don't see too many
SQL 86 joins.  Would you like to write a documentation patch?


Reply | Threaded
Open this post in threaded view
|

Re: Change JOIN tutorial to focus more on explicit joins

Jürgen Purtz
On 20.05.20 23:56, Thomas Munro wrote:

> On Thu, May 21, 2020 at 1:37 AM PG Doc comments form
> <[hidden email]> wrote:
>> The following documentation comment has been logged on the website:
>>
>> Page: https://www.postgresql.org/docs/12/tutorial-join.html
>> Description:
>>
>> The tutorial about joins makes the following statement about the explicit
>> JOIN operator:
>>
>>> This syntax is not as commonly used as the one above
>> I think in 2020 this claim is no longer true, and I would love to see the
>> manual prefer the "modern" explicit JOIN operator rather than sticking to
>> the ancient implicit joins in the WHERE clause.
> +1
>
> The "new" syntax is 28 years old, from SQL 92.  I don't see too many
> SQL 86 joins.  Would you like to write a documentation patch?
>
>
The attached patch

- prefers the explicit join-syntax over the implicit one and explains
the keywords of the explicit syntax

- uses a more accurate definition of 'join'

- separates <programlisting> and <screen> tags

- shifts <indexterm> definitions outside of <para> to get a better
rendering in PDF

- adds a note concerning IDs and foreign keys


--

J. Purtz



0001-query.patch (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Change JOIN tutorial to focus more on explicit joins

Thomas Munro-5
On Wed, May 27, 2020 at 8:29 PM Jürgen Purtz <[hidden email]> wrote:

> > The "new" syntax is 28 years old, from SQL 92.  I don't see too many
> > SQL 86 joins.  Would you like to write a documentation patch?
> >
> >
> The attached patch
>
> - prefers the explicit join-syntax over the implicit one and explains
> the keywords of the explicit syntax
>
> - uses a more accurate definition of 'join'
>
> - separates <programlisting> and <screen> tags
>
> - shifts <indexterm> definitions outside of <para> to get a better
> rendering in PDF
>
> - adds a note concerning IDs and foreign keys

Hi Jürgen,

Please add to the commitfest app, so we don't lose track of it.


Reply | Threaded
Open this post in threaded view
|

Re: Change JOIN tutorial to focus more on explicit joins

Peter Eisentraut-6
In reply to this post by Jürgen Purtz
On 2020-05-27 10:29, Jürgen Purtz wrote:

> The attached patch
>
> - prefers the explicit join-syntax over the implicit one and explains
> the keywords of the explicit syntax
>
> - uses a more accurate definition of 'join'
>
> - separates <programlisting> and <screen> tags
>
> - shifts <indexterm> definitions outside of <para> to get a better
> rendering in PDF
>
> - adds a note concerning IDs and foreign keys

I have committed some parts of this patch:

 > - separates <programlisting> and <screen> tags

 > - shifts <indexterm> definitions outside of <para> to get a better
 > rendering in PDF

as well as the change of W1/W2 to w1/w2.  (Note that there is also
src/tutorial/basics.source that should be adjusted in the same way.)

For the remaining patch I have a couple of concerns:

 >      <para>
 >       Attempt to determine the semantics of this query when the
 > -     <literal>WHERE</literal> clause is omitted.
 > +     <literal>ON</literal> clause is omitted.
 >      </para>
 >     </formalpara>

This no longer works.

In general, I agree that some more emphasis on the JOIN syntax is okay.
But I think the order in which the tutorial has taught it so far is
okay: First you do it the manual way, then you learn the more abstract way.

 > +   <note>
 > +    <para>
 > +     The examples shown here combine rows via city names.
 > +     This should help to understand the concept. Professional
 > +     solutions prefer to use numerical IDs and foreign keys
 > +     to join tables.
 > +    </para>
 > +   </note>

While there are interesting debates to be had about natural vs.
surrogate keys, I don't think we should imply that one of them is
unprofessional and then leave it at that and give no further guidance.
I think we should leave this out.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Change JOIN tutorial to focus more on explicit joins

Jürgen Purtz
On 04.09.20 08:52, Peter Eisentraut wrote:

>
> For the remaining patch I have a couple of concerns:
>
> >      <para>
> >       Attempt to determine the semantics of this query when the
> > -     <literal>WHERE</literal> clause is omitted.
> > +     <literal>ON</literal> clause is omitted.
> >      </para>
> >     </formalpara>
>
> This no longer works.
>
Ok, but I don't have any better suggestion than to delete this para.
> In general, I agree that some more emphasis on the JOIN syntax is
> okay. But I think the order in which the tutorial has taught it so far
> is okay: First you do it the manual way, then you learn the more
> abstract way.

In this context, I wouldn't use the terms 'manual' and 'abstract', it's
more about 'implicit' and 'explicit' syntax. The 'explicit' syntax does
not only emphasis the aspect of 'joining' tables, it also differentiates
between the usage of following AND/OR/NOT key words as join conditions
or as additional restrictions (the results are identical but not the
semantic). Because the purpose of this patch is the preference of the
explicit syntax, we shall show this syntax first.

>
> > +   <note>
> > +    <para>
> > +     The examples shown here combine rows via city names.
> > +     This should help to understand the concept. Professional
> > +     solutions prefer to use numerical IDs and foreign keys
> > +     to join tables.
> > +    </para>
> > +   </note>
>
> While there are interesting debates to be had about natural vs.
> surrogate keys, I don't think we should imply that one of them is
> unprofessional and then leave it at that and give no further guidance.
> I think we should leave this out.
>
Ok, deleted.

--

Jürgen Purtz


0002-query.patch (6K) Download Attachment