CODEWITHSUNDEEP
mysqljoin
kamikaze_pilot
kamikaze_pilot

Reputation: 14834

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 queries?

Upvotes: 1

Views: 79

Answers (2)

champloo
champloo

Reputation: 66

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.

Upvotes: 1

Jason McCreary
Jason McCreary

Reputation: 72981

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.

Upvotes: 1

Related Questions