Re: IN or BETWEEN

Carlos (miotromailcarlos_at_netscape.net) wrote:
: Hello everyone.
: I am designing a DB with Oracle Designer. When I add 'allowable
: values' for a column I have two choices:
: 1.- Adding values separately (i.e: adding '0' as a 'allowable value'
: and then adding '1' as another 'allowable value'). This will generate
: a check constraint of type IN ( x_column IN (0,1)).
: 2.- Adding values by intervals (i.e: adding '0' as a 'allowable value'
: with '1' as 'high value'). This will generate a check constraint of
: type BETWEEN ( x_column BETWEEN 0 AND 1 ).
: I always assumed that expresions of type 'BETWEEN' are quicker than
: expressions of type 'IN' (at least talking in Oracle terms).
: Am I right in this assumption?
: And yet another question: Do expresions of type 'BETWEEN' work like
: expresions with <= and >= or not?
: Thanks in advance and regards from Spain.

I suggest you use the test that provides the correct logic. If you have a fixed set of values then use IN. If you have a range of values between two end points then use BETWEEN.

Anything else will lead to confusion later.

If you don't like IN then write it differently...

        ( x_column = 0 OR x_column = 1) Received on Fri Apr 26 2002 - 01:52:21 CEST