What means "table A left outer join table B ON TRUE"?

Question

I know conditions are used in table joining. But I met a specific situation and the SQL codes writes like "Table A join table B ON TRUE"

What will happen based on the "ON TRUE" condition? Is that just a total cross join without any condition selection?

Actually, the original expression is like:

Table A LEFT outer join table B on TRUE

Let's say A has m rows and B has n rows. Is there any conflict between "left outer join" and "on true"? Because it seems "on true" results a cross join.

From what I guess, the result will be m*n rows. So, it has no need to write "left outer join", just a "join" will give the same output, right?

Answer 1

Yes. That's the same thing as a CROSS JOIN.

In MySQL, we can omit the [optional] CROSS keyword. We can also omit the ON clause.

The condition in the ON clause is evaluated as a boolean, so we could also jave written something like ON 1=1.

---

UPDATE:

(The question was edited, to add another question about a LEFT [OUTER] JOIN b which is different than the original construct: a JOIN b)

The "LEFT [OUTER] JOIN" is slightly different, in that rows from the table on the left side will be returned even when there are no matching rows found in the table on the right side.

As noted, a CROSS JOIN between tables a (containing m rows) and table b containing n rows, absent any other predicates, will produce a resultset of m x n rows.

The LEFT [OUTER] JOIN will produce a different resultset in the special case where table b contains 0 rows.

CREATE TABLE a (i INT);
CREATE TABLE b (i INT);
INSERT INTO a VALUES (1),(2),(3);

SELECT a.i, b.i FROM a LEFT JOIN b ON TRUE ;

Note that the LEFT JOIN will returns rows from table a (a total of m rows) even when table b contains 0 rows.

Answer 2

A cross join produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you're just joining everything to everything.

Cross join does not combine the rows, if you have 100 rows in each table with 1 to 1 match, you get 10.000 results, Innerjoin will only return 100 rows in the same situation.

These 2 examples will return the same result:

Cross join

select * from table1 cross join table2 where table1.id = table2.fk_id

Inner join

select * from table1 join table2 on table1.id = table2.fk_id

Use the last method