postgresql - 为什么 CROSS JOIN 条件在 'ON' 子句中不起作用，仅在 WHERE 子句中起作用？

Question

我想知道为什么条件交叉联接必须具有 WHERE 子句中指定的条件，以及为什么它在“ON”子句中不起作用。 编译示例见链接:http://rextester.com/IKY8693

业务背景:我需要生成一个介于开始日期和结束日期之间的日期列表，以填补空白，以便针对第三个表进行左联接，以便为特定月份返回零/空值。

我是怎么做到的:让我们以用户表为例，开始日期和结束日期为 YYYYMM。

| user_id | start_yearmonth | end_yearmonth |
|---------|-----------------|---------------|
| u9876   | 201504          | 201610        |
| u5564   | 201602          | 201612        |
| u4435   | 201606          | NULL          |

要交叉连接的表是所需 YYYYMM 日期的表。

| yearmonth |
|-----------|
| 201601    |
| 201602    |
| 201603    |
| 201604    |
| 201605    |
| 201606    |
| 201607    |
| 201608    |
| 201609    |
| 201610    |
| 201611    |
| 201612    |
| 201701    |
| 201702    |

在 where 子句中带有条件的 CROSS JOIN 有效，但当条件在“ON”子句中时这不起作用。这是为什么？

SELECT
    *
FROM
    user_tbl
    CROSS JOIN date_range
WHERE
    user_tbl.start_yearmonth <= date_range.yearmonth
    AND (user_tbl.end_yearmonth >= date_range.yearmonth
         OR user_tbl.end_yearmonth IS NULL)
ORDER BY 
    user_tbl.user_id, date_range.yearmonth ;

Answer 1

CROSS JOIN 是在两个表之间执行完整笛卡尔积的 SQL 运算符。由于是笛卡尔积，运算时不允许任何条件，只能通过一些过滤操作(WHERE条件)限制其结果。

JOIN(INNER 和 OUTER JOIN，即)运算符，只是笛卡尔积和运算符的 ON 部分中表达的过滤运算符(事实上，在 SQL 的原始语法中没有 JOIN 运算符，只是“逗号”符号表示产品的连接条件始终在 WHERE 部分表示)。

例子:

“旧”符号:

SELECT ...
FROM table1 t1, table2 t2
WHERE t1.attribute = t2.attribute

相当于“现代”符号:

SELECT ...
FROM table1 t1 INNER JOIN table2 t2 ON t1.attribute = t2.attribute

同时，对于笛卡尔积:

“旧”符号:

SELECT ...
FROM table1 t1, table2 t2

相当于“现代”符号:

SELECT ...
FROM table1 t1 CROSS JOIN table2 t2

换句话说，需要条件的 CROSS JOIN 实际上是某种 INNER JOIN。