-1

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, would it be stylistically more readable to 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 on SO which contained conflicting advice re part 1 of my question.

SMGreenfield
  • 1,680
  • 19
  • 35
  • 2
    ON clause may contain absolutely any expression whose output/result can be treated as boolean. Including 2 equations combined by OR operator. Due to operator precedence you not must but may use parenthesis in your expression. – Akina Feb 19 '22 at 22:38
  • 1
    Prototype it in a dummy environment. Even use dbfiddle.uk or similar. There are a multitude of ways to try this yourself. Even with an answer here, even from BillKarwin, I wouldn't trust the answer until trialled and tested myself. – MatBailie Feb 19 '22 at 23:28
  • You can sandbox in sites like the SO-associated sqlfiddle.com. Besides the [help] info you can google (using 'site:') re how the site(s) work at [so], [meta] & [meta.se]. The SO/SE search functionality is essentially literal text so of limited help. PS [mre] [ask] [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) PS See the voting arrow mouseover texts. PS Saying just that you searched is not helpful. – philipxy Feb 19 '22 at 23:32
  • Does this answer your question? [SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or) – philipxy Feb 19 '22 at 23:45
  • @philipxy-- I appreciate the suggestions and feedback. Re order of Precedence, SQL logic is similar to most languages, so I wasn't expecting a difference there -- I should have framed that portion as a stylistic question. But regarding sandboxing, the code in question is part of a more complex Python / WSGI API up on AWS. I was surprised to find other answers on SO contradicted each other on this very issue, so I wanted to be on very solid ground. – SMGreenfield Feb 20 '22 at 04:03
  • That comment is odd. The variety of links etc in my 1st comment (the 2nd is auto-generated by my flagging as a duplicate) reflects that it & comments have many flaws. A post should ask 1 question. "the code in question" is 1 line. Research effort is expected & reasonable programming research is reading the manual. The nth duplicate of a question is "not helpful". If this "showed any research effort" some "contradiction" evidence would be in your post. Etc. (But I repeat myself.) PS As bad as SO posts can be I'd be interested to see reasonably received SO answers "conflicting" OR is SQL. – philipxy Feb 20 '22 at 04:58
  • @philipxy -- What I saw that was conflicting was argument(s) in the comments that OR could be used in the ON statement. I'm so sorry this question has created so much concern. I looked at a number of mysql on-line documents expecting at least one of them to show an example where "OR" (or AND) was used, but in the multiple documents I research, I found none. At this point I would be quite pleased if you wished to delete the entire question, since it has run afoul of so man SO rules. – SMGreenfield Feb 20 '22 at 06:17
  • That comment too seems odd. Just googling `sql "OR"` returns examples. (You need to know that double quotes stop search engines from ignoring common words.) PS The MySQL manual is (easily found to be) [here](https://dev.mysql.com/doc/refman/8.0/en/). PS The issue is not "running afoul of rules" pe se but about people posting posts that benefit the site ie future searchers & also you. PS "What I saw that was conflicting was argument(s) in the comments that OR could be used in the ON" It seems more likely that you are misinterpreting than that someone thinks OR is not available. Bye. – philipxy Feb 20 '22 at 09:37

1 Answers1

1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828