Re: Change JOIN tutorial to focus more on explicit joins

[Pavel Stehule]

čt 22. 10. 2020 v 18:27 odesílatel David G. Johnston <
david.g.johns...@gmail.com> napsal:

> On Thu, Oct 22, 2020 at 8:14 AM Pavel Stehule 
> wrote:
>
>> Why do you use parenthesis for ON clause?  It is useless. SQL is not C or
>> JAVA.
>>
>>
> At this point in my career it's just a personal habit.  I never programmed
> C, done most of my development in Java so maybe that's a subconscious
> influence?
>
> I suspect it is partly because I seldom need to use "ON" but instead join
> with "USING" which does require the parentheses, so when I need to use ON I
> just keep them.
>
> I agree they are unnecessary in the example and should be removed to be
> consistent.
>

:)



> David J.
>
>

Re: Change JOIN tutorial to focus more on explicit joins

[David G. Johnston]

On Thu, Oct 22, 2020 at 8:14 AM Pavel Stehule 
wrote:

> Why do you use parenthesis for ON clause?  It is useless. SQL is not C or
> JAVA.
>
>
At this point in my career it's just a personal habit.  I never programmed
C, done most of my development in Java so maybe that's a subconscious
influence?

I suspect it is partly because I seldom need to use "ON" but instead join
with "USING" which does require the parentheses, so when I need to use ON I
just keep them.

I agree they are unnecessary in the example and should be removed to be
consistent.

David J.

Re: Change JOIN tutorial to focus more on explicit joins

[Pavel Stehule]

čt 22. 10. 2020 v 15:32 odesílatel Jürgen Purtz  napsal:

> On 22.10.20 01:40, David G. Johnston wrote:
>
> On Fri, Sep 4, 2020 at 2:36 AM Jürgen Purtz  wrote:
>
>> On 04.09.20 08:52, Peter Eisentraut wrote:
>> >
>> > For the remaining patch I have a couple of concerns:
>>
>
> This patch should not be changing the formatting choices for these
> queries, just the addition of a JOIN clause and modification of the WHERE
> clause.  Specifically, SELECT is left-aligned while all subsequent clauses
> indent under it.  Forced alignment by adding extra spaces isn't done here
> either.  I have not altered those in the attached.
>
> Did some word-smithing on the first paragraph.  The part about the
> cross-join was hurt by "in some way" and "may be" is not needed.
>
> Pointing out that values from both tables doesn't seem like an improvement
> when the second item covers that and it is more specific in noting that the
> city name that is joined on appears twice - once from each table.
>
> ON expression is more precise and the reader should be ok with the term.
>
> Removal of the exercise is good.  Not the time to discuss cross join
> anyway.  Given that "ON true" works the cross join form isn't even required.
>
> In the FROM clause form I would not add table prefixes to the column
> names.  They are not part of the form changing.  If discussion about table
> prefixing is desired it should be done explicitly and by itself.  They are
> used later on, I didn't check to see whether that was covered or might be
> confusing.
>
> I suggested a wording for why to use join syntax that doesn't involve
> legacy and points out its merit compared to sticking a join expression into
> the where clause.
>
> The original patch missed having the syntax for the first left outer join
> conform to the multi-line query writing standard you introduced.  I did not
> change.
>
> The "AND" ON clause should just go with (not changed):
>
> ON (w1.temp_lo < w2.temp_lo
> AND w1.temp_hi > w2.temp_high);
>
> Attaching my suggestions made on top of the attached original
> 0002-query.patch
>
> David J.
>
> (Hopefully) I have integrated all of David's suggestions as well as the
> following rules:
>
> - Syntax formatting with the previously used 4 spaces plus newline for JOIN
>
> - Table aliases only when necessary or explicitly discussed
>
> The discussion about the explicit vs. implicit syntax is added to the "As
> join expressions serve a specific purpose ... " sentence and creates a
> paragraph of its own.
>
> The patch is build on top of master.
>

Why do you use parenthesis for ON clause?  It is useless. SQL is not C or
JAVA.

Regards

Pavel

--
> J. Purtz
>
>
>

Re: Change JOIN tutorial to focus more on explicit joins

[Jürgen Purtz]

On 22.10.20 01:40, David G. Johnston wrote:
On Fri, Sep 4, 2020 at 2:36 AM Jürgen Purtz > wrote:


On 04.09.20 08:52, Peter Eisentraut wrote:
>
> For the remaining patch I have a couple of concerns:


This patch should not be changing the formatting choices for these 
queries, just the addition of a JOIN clause and modification of the 
WHERE clause.  Specifically, SELECT is left-aligned while all 
subsequent clauses indent under it.  Forced alignment by adding extra 
spaces isn't done here either.  I have not altered those in the attached.


Did some word-smithing on the first paragraph.  The part about the 
cross-join was hurt by "in some way" and "may be" is not needed.


Pointing out that values from both tables doesn't seem like an 
improvement when the second item covers that and it is more specific 
in noting that the city name that is joined on appears twice - once 
from each table.


ON expression is more precise and the reader should be ok with the term.

Removal of the exercise is good.  Not the time to discuss cross join 
anyway.  Given that "ON true" works the cross join form isn't even 
required.


In the FROM clause form I would not add table prefixes to the column 
names.  They are not part of the form changing.  If discussion about 
table prefixing is desired it should be done explicitly and by 
itself.  They are used later on, I didn't check to see whether that 
was covered or might be confusing.


I suggested a wording for why to use join syntax that doesn't involve 
legacy and points out its merit compared to sticking a join expression 
into the where clause.


The original patch missed having the syntax for the first left outer 
join conform to the multi-line query writing standard you introduced.  
I did not change.


The "AND" ON clause should just go with (not changed):

ON (w1.temp_lo < w2.temp_lo
    AND w1.temp_hi > w2.temp_high);

Attaching my suggestions made on top of the attached original 
0002-query.patch


David J.

(Hopefully) I have integrated all of David's suggestions as well as the 
following rules:


- Syntax formatting with the previously used 4 spaces plus newline for JOIN

- Table aliases only when necessary or explicitly discussed

The discussion about the explicit vs. implicit syntax is added to the 
"As join expressions serve a specific purpose ... " sentence and creates 
a paragraph of its own.


The patch is build on top of master.

--

J. Purtz


diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml
index e73e805ec4..532427ab4e 100644
--- a/doc/src/sgml/query.sgml
+++ b/doc/src/sgml/query.sgml
@@ -440,13 +440,12 @@ SELECT DISTINCT city
 

 Thus far, our queries have only accessed one table at a time.
-Queries can access multiple tables at once, or access the same
-table in such a way that multiple rows of the table are being
-processed at the same time.  A query that accesses multiple rows
-of the same or different tables at one time is called a
-join query.  As an example, say you wish to
-list all the weather records together with the location of the
-associated city.  To do that, we need to compare the city
+Queries which access multiple tables (including repeats) at once are called
+join queries.  They internally combine
+each row from one table with each row of a second table.  An expression is
+specified to limit which pairs of rows are returned.
+For example, to return all the weather records together with the location of the
+associated city, the database compare the city
 column of each row of the weather table with the
 name column of all rows in the cities
 table, and select the pairs of rows where these values match.
@@ -461,10 +460,17 @@ SELECT DISTINCT city
 
 
 SELECT *
-FROM weather, cities
-WHERE city = name;
+FROM weather
+JOIN cities ON (city = name);
 
 
+After the keyword ON follows the
+expression comparing their rows. In this case, the
+definition how to compare their rows. In this case, the
+column city of table weather
+must be equal to the column name
+of table cities.
+
 
  city  | temp_lo | temp_hi | prcp |date| name  | location
 ---+-+-+--++---+---
@@ -499,23 +505,14 @@ SELECT *
*:
 
 SELECT city, temp_lo, temp_hi, prcp, date, location
-FROM weather, cities
-WHERE city = name;
+FROM weather
+JOIN cities ON (city = name);
 
   
  
 

 
-   
-Exercise:
-
-
- Attempt to determine the semantics of this query when the
- WHERE clause is omitted.
-
-   
-

 Since the columns all had different names, the parser
 automatically found which table they belong to.  If there
@@ -526,8 +523,8 @@ SELECT city, temp_lo, temp_hi, prcp, date, location
 
 SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, 

Re: exceptions

[Jürgen Purtz]

On 21.10.20 18:17, Tom Lane wrote:

PG Doc comments form  writes:

Description:
02000   no_data
should read no_data_found

Looks correct as stated to me.  plpgsql's ERRCODE_NO_DATA_FOUND
(P0002) is a different animal.

regards, tom lane



Yes, our documentation correlates to the SQL standard (as of 2011):

--

J. Purtz