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 ;

What do you mean? Cross join doesn't support `on`. You can think of it as a Cartesian product of two sets, `on` doesn't make sense. — freakish, Jun 08 '17 at 13:45
I think my question is clear - I'm specifically asking **WHY** cross join doesn't support 'ON'. 'ON' is used to create conditions between two tables, is it not? — psrpsrpsr, Jun 08 '17 at 13:55
Yes, and you don't create any condition with cross join. Other types of joins have `on` condition **mandatory**. Simple as that. If you wish to add a condition on the cross join then you have to do it outside the cross join statement. — freakish, Jun 08 '17 at 13:59
That is not a cross join. It is an inner join with a cartesian product to a set of date_range records that match the user_tbl record. The only reason to say "CROSS JOIN" is so that you do not have to specify an "ON" clause if you want a full cartesian product of all rows. Personally I want to say "CROSS LEFT JOIN" but that's another story. — PhilHibbs, Sep 19 '17 at 12:11

Renzo · Accepted Answer · 2017-06-14T06:45:27.993

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.

Note that OUTER joins, while still based on the cartesian product, differ from INNER or CROSS JOINS by also performing UNION operation(s) as needed, to add rows for the missing left/right/both rows. — petre, Mar 02 '22 at 11:16

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

1 Answers1

Linked