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?

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

Solution 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

Solution 3

The join syntax's general form:

SELECT *
FROM   table_a
JOIN   table_b ON condition

The condition is used to tell the database how to match rows from table_a to table_b, and would usually look like table_a.some_id = table_b.some_id.

If you just specify true, you will match every row from table_a with every row of table_b, so if table_a contains n rows and table_b contains m rows the result would have m*n rows.

Most(?) modern databases have a cleaner syntax for this, though:

SELECT     *
FROM       table_a
CROSS JOIN table_b

Solution 4

The difference between the pure cross join and left join (where the condition is forced to be always true, as when using ON TRUE) is that the result set for the left join will also have rows where the left table's rows appear next to a bunch of NULLs where the right table's columns would have been.

View more solutions

Comments

[lgb7676]

Thanks. What if the expression is "Table A LEFT outer join table B on TRUE" Is the result the same? like m*n rows?

[Bowen Liu]

What if there is a row in table_a that has zero matching values from table_b? Would it have n rows from table_b that are all null matched to that one row? Thanks.

[Mureinik]

@BowenLiu if the condition is "true" (or you use a cross join), there is no such thing as a row with no matches - all the rows from table a match all the rows from table b, regardless of their content

[Bowen Liu]

Thanks. You got me thinking about the ON condition part. So we can also pass a query like LEFT JOIN...ON table_a.columnA > table_b.columnB, and only the rows that meet the condition will be joined?