0

Recently I observed the following syntax:

AND TRUNC (SYSDATE) + 1 BETWEEN a(+) AND b(+)

I know that the (+) sign is used to address left or right join in conditions like:

and a = b(+)

... but I have no idea what it means in the BETWEEN function.

Can someone explain, please, or better give an example using BETWEEN function?

Thank you,

mikcutu
  • 1,013
  • 2
  • 17
  • 34
  • 3
    It means that you are using archaic outer join syntax. Switch to proper, explicit `outer join` syntax. – Gordon Linoff Sep 19 '17 at 11:44
  • 1
    @Gordon Linoff: I know it is an archaic outer join syntax but how it works in BETWEEN function? – mikcutu Sep 19 '17 at 13:48
  • @i3rutt: "a" or "b" are not functions. in my example, they are columns in tables – mikcutu Sep 19 '17 at 13:49
  • see other question/answer.. in your case it implies that columns A and B are on the outer table and thus could be null. `x BETWEEN NULL and NULL` I believe will be false – ShoeLace Sep 20 '17 at 01:37
  • It means the same thing in a `between` expression as it does with `=`. Presumably `a` and `b` are columns in an outer-joined table, and the query needs to avoid excluding rows from the driving table where no corresponding row exists in the outer-joined table. – William Robertson Sep 20 '17 at 18:14

1 Answers1

0

The Oracle docs says:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331