In mysql, can ON statement in an INNER JOIN accept an OR clause?-CodePudding

Can an ON clause of an INNER JOIN accept an AND?

In the following (and presently working) mysql example:

SELECT p.pk_ProductID
FROM Product p
INNER JOIN SerialNumber sn     
ON sn.fk_ProductID = p.pk_ProductID     
WHERE sn.pk_SerialNumberID = %s AND p.ProductGUID = %s     
LIMIT 1

Is it legit to add an OR clause with sn.fk_ProductID2 like so:

ON sn.fk_ProductID = p.pk_ProductID OR sn.fk_ProductID2 = p.pk_ProductID

If legit, should it be in parenthesis:

ON (sn.fk_ProductID = p.pk_ProductID) OR (sn.fk_ProductID2 = p.pk_ProductID)

NOTE: I have reviewed several seemingly similar questions which contain conflicting advice.

CodePudding user response：

It's legal to write a query like you show. The expression following the ON keyword can be any boolean expression. Strictly speaking, it doesn't even have to reference either table you are joining.

It is not necessary to use parentheses, because the operator precedence of = versus OR is clearly that = binds tighter. See https://dev.mysql.com/doc/refman/8.0/en/operator-precedence.html for details on that.