Different LEFT JOIN results with and without USING

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Different LEFT JOIN results with and without USING
Date: 2017-02-21 14:33:09
Message-ID: CAADeyWhrobaSsDjPxqPiXNt2H9o61SFvjLq1k=Lx6UvvR1Eg8g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Melvin Davidson 2017-02-21 14:38:30 Re: Move rows from one database to other
Previous Message William Ivanski 2017-02-21 14:27:14 Re: Move rows from one database to other