过滤条件写在ON里和写在WHERE里有什么不同？

filter_location_matters_in_left_join.sql

/*
 LEFT JOIN without filter, all tuples in foo can find a match in bar
 */
WITH foo (id, bar_id) AS
         (
             SELECT 1, 1
             UNION ALL
             SELECT 2, 1
             UNION ALL
             SELECT 3, 2
             UNION ALL
             SELECT 4, 2
         ),
     bar (id, name) AS
         (
             SELECT 1, 'a'
             UNION ALL
             SELECT 2, 'b'
             UNION ALL
             SELECT 3, 'c'
             UNION ALL
             SELECT 4, 'd'
         )
SELECT *
FROM foo
         LEFT JOIN bar
                   ON foo.bar_id = bar.id;
-- 1,1,1,a
-- 2,1,1,a
-- 3,2,2,b
-- 4,2,2,b


/*
 Put a filter in WHERE clause, same result set as above query then got filtered with bar.id = 2, 2 of 4 rows retained
 */
WITH foo (id, bar_id) AS
         (
             SELECT 1, 1
             UNION ALL
             SELECT 2, 1
             UNION ALL
             SELECT 3, 2
             UNION ALL
             SELECT 4, 2
         ),
     bar (id, name) AS
         (
             SELECT 1, 'a'
             UNION ALL
             SELECT 2, 'b'
             UNION ALL
             SELECT 3, 'c'
             UNION ALL
             SELECT 4, 'd'
         )
SELECT *
FROM foo
         LEFT JOIN bar
                   ON foo.bar_id = bar.id
WHERE bar.id = 2;
-- 3,2,2,b
-- 4,2,2,b


/*
 Put a filter in ON clause, only foo.bar_id = bar.id AND bar.id = 2 can get a match.
 Since it's LEFT JOIN, all the tuples from table foo retained, unmatched ones will get NULL value for column from bar
 */
WITH foo (id, bar_id) AS
         (
             SELECT 1, 1
             UNION ALL
             SELECT 2, 1
             UNION ALL
             SELECT 3, 2
             UNION ALL
             SELECT 4, 2
         ),
     bar (id, name) AS
         (
             SELECT 1, 'a'
             UNION ALL
             SELECT 2, 'b'
             UNION ALL
             SELECT 3, 'c'
             UNION ALL
             SELECT 4, 'd'
         )
SELECT *
FROM foo
         LEFT JOIN bar
                   ON foo.bar_id = bar.id AND bar.id = 2;
-- 1,1,<null>,<null>
-- 2,1,<null>,<null>
-- 3,2,2,b
-- 4,2,2,b

过滤条件写在WHERE里，先按照等值进行关联。关联完成后的结果集，再进行一轮过滤。
过滤条件写在ON里，决定是否关联成功。关联不成功的情况，对于LEFT JOIN，左表信息保留，右表信息为NULL。

对于INNER JOIN，两种写法的关系代数表达式是等价的，过滤条件可以进一步下推到Table Scan的过程中。