convert sql BETWEEN clause from ANSI 89 into ANSI 92

Question

I am trying to convert an sql statement from ANSI 89 into ANSI 92 (meaning to transofrm the "(+)" into "OUTHER JOIN")

Here is the code:

select a.*, p.price
from article a, prices p
where a.product_id = p.product_id(+)
and trunc(sysdate) + 1 between a.date_from and date_to
and trunc(sysdate) + 1 between p.date_from(+) and p.date_to(+);

I know that (+) refers in LEFT or RIGHT JOIN, depending of the possition where it is placed but I cannot get it how to transform the last line ( and trunc(sysdate) + 1 between p.date_from(+) and p.date_to(+) )

Until now, I did the follwoing:

select a.*, p.price
from article a
left join prices p
on a.product_id = p.product_id
where trunc(sysdate) + 1 between a.date_from and date_to

but I hae no clue how to transform the last condition.

Can someone help with this, please?

Thank you,

If you use a `left join`, that `(+)` isn't needed anymore. About the different ansi92 joins, [here's an interesting older post](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join?rq=1) you could read. Also, it's possible to use a `between` criteria in the `on` of a `left join` (although rarely done that way) — LukStorms, Nov 08 '17 at 08:59
`and trunc(sysdate) + 1 between p.date_from and p.date_to;`? — , Nov 08 '17 at 09:08
@LukStorms: my mistake, I forgot an "(+)" into my try. Anyway, the article is good but already knwo the difference among LEFT/RIGHT/INNER/FULL joins. I just want transform the and trunc(sysdate) + 1 between p.date_from(+) and p.date_to(+) in an ANSI 92 — mikcutu, Nov 08 '17 at 09:14
@a_horse_with_no_name: it is not working. it restricts the rest of the conditions and returns nothing. — mikcutu, Nov 08 '17 at 09:16
You need to put that into the `JOIN` condition, **not** the `where` condition. — , Nov 08 '17 at 09:17
@a_horse_with_no_name: already done. it returns me extra rows than in ANSI 89 version — mikcutu, Nov 08 '17 at 09:19
As an aside, the (+) operator isn't ANSI-anything; your original query is Oracle-specific, not ANSI-89 — Mark Adelsberger, Nov 08 '17 at 15:15

score 0 · Answer 1 · answered Nov 08 '17 at 14:40

I'm assuming that each table has a date_from and a date_to, and that you mean to compare against both ranges. In that case you left an a. off in your example query, so I've added it. Of course feel free to adjust if that isn't what you meant...

So the obvious difficulty is that if you add

and trunc(sysdate) + 1 between p.date_from and p.date_to

to the where clause, then any "generated NULLs" record would be mishandled by that condition. So instead you can add it to the outer join condition

select a.*, p.price
  from           article a
       left join prices p
    on a.product_id = p.product_id
   and trunc(sysdate) + 1 between p.date_from and p.date_to
 where trunc(sysdate) + 1 between a.date_from and a.date_to

Semantically this is a bit of a hack, because the date range check on p arguably isn't really a join condition; but this applies the "outer" semantics to the condition. The other, more explicit option would be

select a.*, p.price
  from           article a
       left join prices p
    on a.product_id = p.product_id
 where trunc(sysdate) + 1 between a.date_from and a.date_to
   and (trunc(sysdate) + 1 between p.date_from and p.date_to
        or p.product_id is null)

(You might be able to drop the or and just say between coalesce(p.date_from, trunc(sysdate)) and coalesce(p.date_to, trunc(sysdate)+2)) or something like that, but this assumes that only generated-NULL records would have NULL for those values - i.e. that no prices record actually has a NULL in those columns.)

convert sql BETWEEN clause from ANSI 89 into ANSI 92

1 Answers1