I recently experimented with MySQL queries involving INNER JOIN and CROSS JOIN, both without any specified conditions. Surprisingly, both queries produced the same result for my dataset.
SELECT * FROM untitled_table INNER JOIN untitled_table2;
SELECT * FROM untitled_table CROSS JOIN untitled_table2;
I observed that both CROSS JOIN and INNER JOIN can be executed without any conditions, and they still yield identical results. Even more intriguingly, MySQL Workbench does not throw any errors when I run INNER JOIN without a condition or CROSS JOIN with a condition.
Is there any fundamental difference between these two join types when used without conditions?
If there's no distinction, why does MySQL have two keywords for what seems to be the same operation?
Moreover, when I applied conditions to both CROSS JOIN and INNER JOIN, they still produced matching results.
Why do these seemingly different join types behave similarly in this scenario?
As you have mentioned, in MySQL an inner join with no ON conditions behaves identically to a cross join. It is simply a syntax quirk within MySQL and not much more than this. It should be noted that in most other databases (e.g. Postgres), an inner join without join criteria results in a syntax compilation error and will not run.
As to which version you should use in MySQL, I would opine that you should use CROSS JOIN, because it makes it explicit and clear what your intentions are. Using INNER JOIN without criteria, as you have probably seen yourself, might be confusing to someone who is not fluent in MySQL.