Postgres Pro
Downloads
Home   >   mailing lists

[GENERAL] Different LEFT JOIN results with and without USING - Mailing list pgsql-general

From Alexander Farber
Subject [GENERAL] Different LEFT JOIN results with and without USING
Date
Msg-id CAADeyWhrobaSsDjPxqPiXNt2H9o61SFvjLq1k=Lx6UvvR1Eg8g@mail.gmail.com
Whole thread Raw
Responses Re: [GENERAL] Different LEFT JOIN results with and without USING  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tree view
Good afternoon,

why do these queries please return different results?

    SELECT
        s.gid,
        TO_CHAR(g.created, 'DD.MM.YYYY'),
        TO_CHAR(g.finished, 'DD.MM.YYYY'),
        LENGTH(s.word),
        s.score
    FROM    words_scores s
        LEFT JOIN words_games g ON s.gid = g.gid
        AND   s.uid = 1
        ORDER BY LENGTH(s.word) DESC, s.mid DESC
        LIMIT   20;

vs.

    SELECT
        s.gid,
        TO_CHAR(g.created, 'DD.MM.YYYY'),
        TO_CHAR(g.finished, 'DD.MM.YYYY'),
        LENGTH(s.word),
        s.score
    FROM    words_scores s
        LEFT JOIN words_games g USING(gid)
        WHERE s.uid = 1
        ORDER BY LENGTH(s.word) DESC, s.mid DESC
        LIMIT   20;

Returns:

 gid |  to_char   | to_char | length | score
-----+------------+---------+--------+-------
   1 |            |         |      5 |     8
   1 | 21.02.2017 |         |      5 |    14
   1 | 21.02.2017 |         |      4 |    11
   1 |            |         |      4 |     7
   1 | 21.02.2017 |         |      4 |    24
   1 | 21.02.2017 |         |      3 |     5
   1 |            |         |      3 |    23
   1 |            |         |      3 |    14
   1 | 21.02.2017 |         |      3 |    12
   1 |            |         |      3 |     8
   1 | 21.02.2017 |         |      3 |     8
   1 |            |         |      2 |     6
   1 | 21.02.2017 |         |      2 |     3
   1 | 21.02.2017 |         |      2 |     5
(14 rows)

vs.

 gid |  to_char   | to_char | length | score
-----+------------+---------+--------+-------
   1 | 21.02.2017 |         |      5 |    14
   1 | 21.02.2017 |         |      4 |    11
   1 | 21.02.2017 |         |      4 |    24
   1 | 21.02.2017 |         |      3 |     5
   1 | 21.02.2017 |         |      3 |    12
   1 | 21.02.2017 |         |      3 |     8
   1 | 21.02.2017 |         |      2 |     3
   1 | 21.02.2017 |         |      2 |     5
(8 rows)

Here is my words_scores table:

 mid | gid | uid | word  | score
-----+-----+-----+-------+-------
   1 |   1 |   1 | ЖИР   |     8
   2 |   1 |   2 | ЖИР   |     8
   3 |   1 |   1 | МОЩИ  |    24
   4 |   1 |   2 | МОРО  |     7
   5 |   1 |   1 | ПОВОЙ |    14
   6 |   1 |   2 | ПРРИЯ |     8
   7 |   1 |   1 | ЯД    |     5
   7 |   1 |   1 | ДУР   |    12
   8 |   1 |   2 | ПЭР   |    14
   9 |   1 |   1 | ВОРС  |    11
  10 |   1 |   2 | ЛОФ   |    23
  11 |   1 |   1 | ОМ    |     3
  11 |   1 |   1 | СОМ   |     5
  12 |   1 |   2 | УГ    |     6
(14 rows)

# \d words_scores
      Table "public.words_scores"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 mid    | bigint            | not null
 gid    | integer           | not null
 uid    | integer           | not null
 word   | character varying | not null
 score  | integer           | not null
Check constraints:
    "words_scores_score_check" CHECK (score >= 0)
    "words_scores_word_check" CHECK (word::text ~ '^[Ð -Я]{2,}$'::text)
Foreign-key constraints:
    "words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
    "words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE

And here words_games table:

words=> \d words_games;
                                   Table "public.words_games"
  Column  |           Type           |                         Modifiers
----------+--------------------------+-----------------------------------------------------------
 gid      | integer                  | not null default nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone | not null
 finished | timestamp with time zone |
 player1  | integer                  | not null
 player2  | integer                  |
 played1  | timestamp with time zone |
 played2  | timestamp with time zone |
 score1   | integer                  | not null
 score2   | integer                  | not null
 hand1    | character varying[]      | not null
 hand2    | character varying[]      | not null
 pile     | character varying[]      | not null
 letters  | character varying[]      | not null
 values   | integer[]                | not null
 bid      | integer                  | not null
Indexes:
    "words_games_pkey" PRIMARY KEY, btree (gid)
Check constraints:
    "words_games_score1_check" CHECK (score1 >= 0)
    "words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
    "words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid) ON DELETE CASCADE
    "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE
    "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    TABLE "words_notes" CONSTRAINT "words_notes_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE

I don't understand, why "USING ... WHERE" condition is resulting in different set than "s.gid=g.gid AND ..."

Thank you
Alex

pgsql-general by date:

Previous
From: William Ivanski
Date:
Subject: Re: [GENERAL] Move rows from one database to other
Next
From: Melvin Davidson
Date:
Subject: Re: [GENERAL] Move rows from one database to other