What are the possible conditions for an "ON" statement in a SQL table join?

I am not asking for any specific SQL database, just in general. I have searched all over the web, and I can not find anything. All I ever see in documentation and examples is JOIN ON a.a = b.b, or sometimes, JOIN ON a.a != b.b.

Is it possible to use any operator valid in a WHERE clause? For example, are the following valid SQL?

1. JOIN ON a.a > b.b
2. JOIN ON a.a >= b.b
3. JOIN ON a.a < b.b
4. JOIN ON a.a <= b.b
5. JOIN ON a.a IS NOT b.b (if matching on NULL?)
6. JOIN ON a.a IS b.b (if matching on NULL?)
7. JOIN ON a.a IN ('1', '2', '3')
8. JOIN ON a.a IS NULL
9. JOIN ON a.a IS NOT NULL

I found this question on Stack Overflow, which HINTS this might be possible, but I have yet to find a concrete answer to this question anywhere: Multiple conditions in SQL joins

Can someone help please? What are valid conditions on a JOIN statement?

CodePudding user response：

The join condition (the part after ON) is a boolean expression.

So every valid boolean expression is a valid join condition.

CodePudding user response：

Every valid boolean expression can be used in a join condition, so that also includes boolean expressions with ANY and ALL.