Why do CROSS JOIN conditions not work in the 'ON' clause, only the WHERE clause?

Question

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.

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          |

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    |

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 ;

Answer 1

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 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).

Examples:

"old" notation:

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

equivalent to the "modern" notation:

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

while, for the cartesian product:

"old" notation:

SELECT ...
FROM table1 t1, table2 t2

equivalent to the "modern" notation:

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.