Menu
For LEFT JOIN you must have ON but you can use ON TRUE . Which causes the join to be the equivalent of a cross join...... there simply is no point to using left join without a qualification to that join where some rows are matched and some might not be matched. ON TRUE does not permit some rows to be unmatched.
Yes, you can! The longer answer is yes, there are a few ways to combine two tables without a common column, including CROSS JOIN (Cartesian product) and UNION. The latter is technically not a join but can be handy for merging tables in SQL.
An on clause is required since you are "joining", and you need to tell which columns you want to join by. Otherwise you would use traditional from without any where condition to all possible row combinations. But you wanted a join, right?
MySQL documentation covers this topic.
Here is a synopsis. When using join or inner join, the on condition is optional. This is different from the ANSI standard and different from almost any other database. The effect is a cross join. Similarly, you can use an on clause with cross join, which also differs from standard SQL.
A cross join creates a Cartesian product -- that is, every possible combination of 1 row from the first table and 1 row from the second. The cross join for a table with three rows ('a', 'b', and 'c') and a table with four rows (say 1, 2, 3, 4) would have 12 rows.
In practice, if you want to do a cross join, then use cross join:
from A cross join B
is much better than:
from A, B
and:
from A join B -- with no on clause
The on clause is required for a right or left outer join, so the discussion is not relevant for them.
If you need to understand the different types of joins, then you need to do some studying on relational databases. Stackoverflow is not an appropriate place for that level of discussion.
See some example in http://www.sitepoint.com/understanding-sql-joins-mysql-database/
You can use 'USING' instead of 'ON' as in the query
SELECT * FROM table1 LEFT JOIN table2 USING (id);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With