Would be nice if using the database query builder to add multiple join conditions, if we could use an OR operator instead of only the AND operator. This would save me having to do many custom queries, unless maybe there is already a way to do this in the query builder i am not seeing?
Hey Paul,
using OR there (with multiple join conditions) can really degrade the performance especially on tables with many records.
It’s recommended and considered better (performance wise) to use two joins and UNION instead of OR
Example:
SELECT *
FROM table1 AS t1
INNER JOIN table2 AS t2 ON (t2.value = t1.value1)
UNION
SELECT *
FROM table1 AS t1
INNER JOIN table2 AS t2 ON (t2.value = t1.value2)
5 Likes
Ahh, thanks Teo, it makes sense then why it wasn’t added into the standard builder, I thought it was just an oversight, haha.
Thanks Teo… I’ve never used unions before. This certainly opened up an alternative to the complicated custom queries. The explanation regarding performance is appreciated very much!