From tara@vilaj.com Sun Dec 30 18:55:08 2001 Received: from mailout6.nyroc.rr.com (mailout6-0.nyroc.rr.com [24.92.226.125]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBUIt8m42042 for ; Sun, 30 Dec 2001 13:55:08 -0500 (EST) (envelope-from tara@vilaj.com) Received: from vilaj.com (cm-24-169-14-97.nycap.rr.com [24.169.14.97]) by mailout6.nyroc.rr.com (8.11.6/Road Runner 1.12) with ESMTP id fBUIt7819214 for ; Sun, 30 Dec 2001 13:55:08 -0500 (EST) Message-ID: <3C2F630B.5060500@vilaj.com> Date: Sun, 30 Dec 2001 13:55:07 -0500 From: Tara Piorkowski User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en-US; rv:0.9.7) Gecko/20011221 X-Accept-Language: en-us MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: RIGHT JOIN Table Ordering Question Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200112/266 X-Sequence-Number: 6088 Hello. I have a question about the ordering of joining of tables using the RIGHT JOIN syntax in the FROM clause of a query. Specifically, I've noticed that if I have one table on which I attempt to right join to two other tables, the ordering of the join conditions is important in the absense of those tables being noted in the WHERE clause. To illustrate, I've prepared the following script... There are two sets of two queries at the end of the script which illustrate my question. In each set of queries there is one query in which a goal_progress_note (the a table) has an existing row and one in which it does not. In the first set, I get my desired result in both queries, the second one being key. That is, everything in tables b and c gets right joined (and the a data is null, of course). However, when I switch the ordering of the two RIGHT JOIN specifications, the second query no longer produces any rows. Obviously, I can get what I need by writing the query the first way. However, I couldn't explain why I got differences, despite doing some unsuccessful research into why this was. Thus, in the interest of understanding fully the RIGHT JOIN/LEFT JOIN sequence (I originally come from an Oracle SQL background, which is different) I would like to understand why the ordering makes a difference. Thanks for any explanations anyone can give. - Tara -- Tara Piorkowski System Administrator, vilaj.com From tgl@sss.pgh.pa.us Sun Dec 30 22:15:39 2001 Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBUMFdm62006 for ; Sun, 30 Dec 2001 17:15:39 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fBUMFPg01528; Sun, 30 Dec 2001 17:15:25 -0500 (EST) To: Tara Piorkowski cc: pgsql-sql@postgresql.org Subject: Re: RIGHT JOIN Table Ordering Question In-reply-to: <3C2F630B.5060500@vilaj.com> References: <3C2F630B.5060500@vilaj.com> Comments: In-reply-to Tara Piorkowski message dated "Sun, 30 Dec 2001 13:55:07 -0500" Date: Sun, 30 Dec 2001 17:15:25 -0500 Message-ID: <1525.1009750525@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200112/267 X-Sequence-Number: 6089 Tara Piorkowski writes: > I have a question about the ordering of joining of tables using the > RIGHT JOIN syntax in the FROM clause of a query. Specifically, I've > noticed that if I have one table on which I attempt to right join to two > other tables, the ordering of the join conditions is important Yup, it sure is. Outer joins are not associative (nor commutative, obviously). When you write (a RIGHT JOIN b) RIGHT JOIN c, you first have the result of the A/B join, which will be guaranteed to produce all the joined rows an inner join would produce, plus a row with null A values for each otherwise unmatched B row. Then you take this and join it to C, again adding rows for each unmatched C row. This is not associative because what's matched or not in the second step may depend on what got added in the first step. In your example, try looking at the whole join results without any WHERE filter: regression=# select * from goal_progress_notes a regression-# RIGHT JOIN goals c USING (goal_id); goal_id | goal_prog_note_id | rating_id | statement | statement ---------+-------------------+-----------+---------------------+---------------------------------------- 1 | 1 | 1 | Tara is doing well. | Will write a correct SQL92 LEFT JOIN. 2 | | | | Will write a correct SQL92 RIGHT JOIN. (2 rows) regression=# select * from goal_progress_notes a regression-# RIGHT JOIN goals c USING (goal_id) regression-# RIGHT JOIN progress_ratings b USING (rating_id); rating_id | goal_id | goal_prog_note_id | statement | statement | description -----------+---------+-------------------+---------------------+---------------------------------------+-------------- 1 | 1 | 1 | Tara is doing well. | Will write a correct SQL92 LEFT JOIN. | Achieved 2 | | | | | Not Achieved (2 rows) I would imagine that what you really want in this example is (goals c LEFT JOIN goal_progress_notes a USING (goal_id)) LEFT JOIN progress_ratings b USING (rating_id) since you definitely want an output row for every goals row whether there are notes or not, and you don't really want output rows for progress ratings that happen to not be used currently (do you)? Depending on your viewpoint about goal progress notes that don't match any goal, perhaps the first join should be a FULL join. > understanding fully the RIGHT JOIN/LEFT JOIN sequence (I originally come > from an Oracle SQL background, which is different) AFAIK Oracle has the same semantics for left/right joins, just an obscure syntax. regards, tom lane