SQL question: BETWEEN or >=
Are there any performance differences in using BETWEEN in a where clause as opposed to >= <= Ex: SELECT * FROM emp WHERE hire_date BETWEEN '01/01/2003' AND today; Or SELECT * FROM emp WHERE hire_date >= '01/01/2003' AND hire_date <= today; Jim Spalla Anexsys Database Administrator 312/441-4036 phone 312/441-4099 fax 815/483-9234 cell phone This transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format.
Shouldn't be. Time it both ways and report back. cheers j. ----- Original Message ----- From: <jspalla@anexsys.com> To: <ids@iiug.org> Sent: Thursday, July 10, 2003 12:58 PM Subject: SQL question: BETWEEN or >= [1533] > Are there any performance differences in using BETWEEN in a where clause as > opposed to >= <= > Ex: SELECT * > FROM emp > WHERE hire_date BETWEEN '01/01/2003' AND today; > > Or SELECT * > FROM emp > WHERE hire_date >= '01/01/2003' > AND hire_date <= today; > > > > Jim Spalla > Anexsys > Database Administrator > 312/441-4036 phone > 312/441-4099 fax > 815/483-9234 cell phone > > This transmission may contain information that is privileged, confidential > and/or exempt from disclosure under applicable law. If you are not the > intended recipient, you are hereby notified that any disclosure, copying, > distribution, or use of the information contained herein (including any > reliance thereon) is STRICTLY PROHIBITED. If you received this transmission > in error, please immediately contact the sender and destroy the material in > its entirety, whether in electronic or hard copy format. > > >
What version of Informix are you running? I believe there may have been an issue with BETWEEN with some of the IDS 7 versions. Christine Normile I/T Specialist, Informix Solutions Data Management Worldwide Sales Support "Jack Parker" <vze2qjg5@verizon.net> Sent by: forum.subscriber@iiug.org 07/10/2003 01:35 PM To: ids@iiug.org cc: Subject: Re: SQL question: BETWEEN or >= [1535] Shouldn't be. Time it both ways and report back. cheers j. ----- Original Message ----- From: <jspalla@anexsys.com> To: <ids@iiug.org> Sent: Thursday, July 10, 2003 12:58 PM Subject: SQL question: BETWEEN or >= [1533] > Are there any performance differences in using BETWEEN in a where clause as > opposed to >= <= > Ex: SELECT * > FROM emp > WHERE hire_date BETWEEN '01/01/2003' AND today; > > Or SELECT * > FROM emp > WHERE hire_date >= '01/01/2003' > AND hire_date <= today; > > > > Jim Spalla > Anexsys > Database Administrator > 312/441-4036 phone > 312/441-4099 fax > 815/483-9234 cell phone > > This transmission may contain information that is privileged, confidential > and/or exempt from disclosure under applicable law. If you are not the > intended recipient, you are hereby notified that any disclosure, copying, > distribution, or use of the information contained herein (including any > reliance thereon) is STRICTLY PROHIBITED. If you received this transmission > in error, please immediately contact the sender and destroy the material in > its entirety, whether in electronic or hard copy format. > > >
I think I was wondering the same thing a few years back and concluded that they were the same - (although BETWEEN is easier for us humans to read) If I recall correctly, I came to this conclusion based on query plans I saw using SET EXPLAIN ON. I suppose there might some insignificant miniscule amount of processing to translate BETWEEN into the <=, >= combo - which I believe it would have to do, but I would think this would be a one-time extremely insignificant thing. ----- Original Message ----- From: "Jack Parker" <vze2qjg5@verizon.net> To: <ids@iiug.org> Sent: Thursday, July 10, 2003 11:35 AM Subject: Re: SQL question: BETWEEN or >= [1535] > Shouldn't be. Time it both ways and report back. > > cheers > j. > ----- Original Message ----- > From: <jspalla@anexsys.com> > To: <ids@iiug.org> > Sent: Thursday, July 10, 2003 12:58 PM > Subject: SQL question: BETWEEN or >= [1533] > > > > Are there any performance differences in using BETWEEN in a where clause > as > > opposed to >= <= > > Ex: SELECT * > > FROM emp > > WHERE hire_date BETWEEN '01/01/2003' AND today; > > > > Or SELECT * > > FROM emp > > WHERE hire_date >= '01/01/2003' > > AND hire_date <= today; > > > > > > > > Jim Spalla > > Anexsys > > Database Administrator > > 312/441-4036 phone > > 312/441-4099 fax > > 815/483-9234 cell phone > > > > This transmission may contain information that is privileged, confidential > > and/or exempt from disclosure under applicable law. If you are not the > > intended recipient, you are hereby notified that any disclosure, copying, > > distribution, or use of the information contained herein (including any > > reliance thereon) is STRICTLY PROHIBITED. If you received this > transmission > > in error, please immediately contact the sender and destroy the material > in > > its entirety, whether in electronic or hard copy format. > > > > > > > >
This is a PGP signed message sent according to RFC3156 [PGP/MIME] --=_Turnpike_MdS$eXBfxuD$Q48R= Content-Type: text/plain;charset=us-ascii;format=flowed Content-Transfer-Encoding: quoted-printable Have a look at the sqlexplain output that should give you a clue as to=20 what a BETWEEN actually does. I had been led to believe that they are functionally the same Regards Colin Dawson In message <200307101955.h6AJtHNs024193@ace.iiug.org>, Christine N....=20 <cnormile@us.ibm.com> writes >What version of Informix are you running? I believe there may have been >an issue with BETWEEN with some of the IDS 7 versions. > >Christine Normile >I/T Specialist, Informix Solutions >Data Management Worldwide Sales Support > > > > > > >"Jack Parker" <vze2qjg5@verizon.net> >Sent by: forum.subscriber@iiug.org >07/10/2003 01:35 PM > > To: ids@iiug.org > cc: > Subject: Re: SQL question: BETWEEN or >=3D [1535] > > >Shouldn't be. Time it both ways and report back. > >cheers >j. >----- Original Message ----- >From: <jspalla@anexsys.com> >To: <ids@iiug.org> >Sent: Thursday, July 10, 2003 12:58 PM >Subject: SQL question: BETWEEN or >=3D [1533] > > >> Are there any performance differences in using BETWEEN in a where clause >as >> opposed to >=3D <=3D >> Ex: SELECT * >> FROM emp >> WHERE hire_date BETWEEN '01/01/2003' AND today; >> >> Or SELECT * >> FROM emp >> WHERE hire_date >=3D '01/01/2003' >> AND hire_date <=3D today; >> --=_Turnpike_MdS$eXBfxuD$Q48R= Content-Type: application/pgp-signature Content-Disposition: attachment; filename=signature.asc -----BEGIN PGP SIGNATURE----- Version: PGPsdk 2.0.5 iQA/AwUAPw7sYwdqJAHg5kIpEQKprACfWojJVlyhFfqF66NLu7HyMsUHh9QAn3p0 CwyJG/x0SvCKgpojDmBz0wPY =Xb0f -----END PGP SIGNATURE----- --=_Turnpike_MdS$eXBfxuD$Q48R=--