SQL : CROSS JOIN is not superset of every JOIN

This can happen when you write program that generate SQL.

When you try to generalize it, you might notice:

A
1
2

B
1
2
3

A LEFT JOIN B ON id = id
1, 1
2, 2

A CROSS JOIN B WHERE id = id
1, 1
2, 2

So you tend to CROSS JOIN everything

Then put everything in the WHERE clause

The NULL is not there

But notice that this is not true

A
1
2

B
1
3

A LEFT JOIN B ON id = id
1, 1
2, NULL

A CROSS JOIN B WHERE id = id
1, 1

If you want that NULL

You might need some work around like

A FULL OUTER JOIN B ON TRUE
WHERE ... statement

You can leave ON in some database or put some TRUE statement like 1=1 as a work around