using OR on mysql JOINs

Is there a way to specify a mysql query that does something like this

SELECT * FROM a LEFT JOIN b ON (a.xyz = b.xyz OR a.xyr = b.xyr);

and therefore returns rows when any of the LEFT JOIN criteria on the ON statement matches up...

if so what would be the syntax of such qu开发者_开发知识库eries?

Split the select statement into two parts and use a UNION.

SELECT * FROM a LEFT JOIN b on a.xyz = b.xyz
UNION
SELECT * FROM a LEFT JOIN b on a.xyr = b.xyr

If the UNION in MySQL is like the UNION in SQL Server, it should produce a set without duplicate rows. Otherwise, you may need to use a DISTINCT.

According the the docs the ON clause is a conditional expression. So yes, you can use such an expression. In fact, exactly what you have should work.

However, depending on your data and join type, it could produce some unintuitive results.