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