为什么CROSS JOIN条件在“ ON”子句中不起作用，仅在WHERE子句中起作用？ [英] Why do CROSS JOIN conditions not work in the 'ON' clause, only the WHERE clause?

问题描述

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

I'm wondering why a conditional cross join must have the condition(s) specified in the WHERE clause, and why it doesn't work in the 'ON' clause. See link for compiled example: http://rextester.com/IKY8693

业务环境：我需要生成一个开始日期和结束日期之间的日期列表，以填补空白，以便与第三张表保持连接，例如零/

Business context: I need to generate a list of dates between a start and end date in order to fill in gaps in order to left join against a third table, such that zeroes/nulls are returned for a particular month.

我是怎么做的：例如，以YYYYMM开始和结束日期为例的用户表。

How I did this: Let's take for example a table of users, with YYYYMM start and end dates.

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

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

The table to be cross joined is a table of desired YYYYMM dates.

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

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

A CROSS JOIN with conditions in the where clause works, but this doesn't work when the conditions are in the 'ON' clause. Why is that?

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 ;

推荐答案

CROSS JOIN是执行完整笛卡尔数的SQL运算符两个表之间的乘积。由于它是笛卡尔乘积，因此在操作过程中不允许任何条件，因此您只能通过某些过滤操作（WHERE条件）来限制其结果。

CROSS JOIN is the SQL operator to perform a full cartesian product between two tables. Since it is a cartesian product, it does not allow any condition during the operation, you can only restrict its result with some filtering operation (the WHERE condition).

JOIN（即INNER和OUTER JOIN）运算符只是笛卡尔积，与在运算符的ON部分表示的过滤运算符一起（实际上，在SQL的原始语法中，没有JOIN运算符，简单地用逗号符号表示产品，其连接条件始终在WHERE部分中表示。）

JOIN (INNER and OUTER JOIN, that is) operators, are simply cartesian product together with the filtering operator expressed in the ON part of the operator (and in fact in the original syntax of SQL there was no JOIN operator, simply the "comma" notation to denote the product with the join condition expressed always in the WHERE part).

示例：

旧符号：

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

换句话说，一个交叉联接需要一个条件实际上是某种内部联接。

In other words, a CROSS JOIN that require a condition is actually some kind of INNER JOIN.

这篇关于为什么CROSS JOIN条件在“ ON”子句中不起作用，仅在WHERE子句中起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！