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

Alexander Farber <alexander.farber@gmail.com> writes:
> why do these queries please return different results?

>     FROM    words_scores s
>         LEFT JOIN words_games g ON s.gid = g.gid
>         AND   s.uid = 1

In this one, "s.uid = 1" is part of the LEFT JOIN condition, so it never
eliminates s rows.  Rows not satisfying the condition will be
null-extended instead.

>     FROM    words_scores s
>         LEFT JOIN words_games g USING(gid)
>         WHERE s.uid = 1

In this one, it's part of the top-level WHERE, so it does eliminate s
rows.

            regards, tom lane