In SQL server (2000 & 2005 ) What is the difference between these two queries ? Which one I should opt on which scenarios ?
Query1:
Select EventId,EventName from EventMaster where EventDate BETWEEN '10/15/2009'
and '10/18/2009'
Query2:
Select EventId,EventName from EventMaster where EventDate >='10/15/2009' and
EventDate <='10/18/2009'
--Edit: second Eventdate was originally missing, hence query syntactically wrong
They are identical: BETWEEN is a shorthand for the longer syntax in the question.
Use an alternative longer syntax where BETWEEN doesn't work e.g.
Select EventId,EventName from EventMaster
where EventDate >='10/15/20009' and EventDate <'10/18/2009'
Typically, there is no difference - the BETWEEN keyword is not supported on all RDBMS platforms, but if it is, the two queries should be identical.
Since they're identical, there's really no distinction in terms of speed or anything else - use the one that seems more natural to you.
I think the only difference is the amount of syntactical sugar on each query. BETWEEN is just a slick way of saying exactly the same as the second query.
There might be some RDBMS specific difference that I'm not aware of, but I don't really think so.
They are the same.
One thing to be careful of, is if you are using this against a DATETIME, the match for the end date will be the beginning of the day:
<= 20/10/2009
is not the same as:
<= 20/10/2009 23:59:59
(it would match against <= 20/10/2009 00:00:00.000)
Logically there are no difference at all. Performance-wise there are -typically, on most DBMSes- no difference at all.
As mentioned by @marc_s, @Cloud, et al. they're basically the same for a closed range.
But any fractional time values may cause issues with a closed range (greater-or-equal and less-or-equal) as opposed to a half-open range (greater-or-equal and less-than) with an end value after the last possible instant.
So to avoid that the query should be rewritten as:
SELECT EventId, EventName
FROM EventMaster
WHERE (EventDate >= '2009-10-15' AND
EventDate < '2009-10-19') /* <<<== 19th, not 18th */
Since BETWEEN doesn't work for half-open intervals I always take a hard look at any date/time query that uses it, since its probably an error.
See this excellent blog post from Aaron Bertrand about why you should change your string format and how the boundary values are handled in date range queries.
I have a slight preference for BETWEEN because it makes it instantly clear to the reader that you are checking one field for a range. This is especially true if you have similar field names in your table. If, aay, our table has both a "transactiondate" and a "transitiondate", if I read "transactiondate between ..." I know immediately that both ends of the test are against this one field. If I read "transactiondate>='2009-04-17' and transactiondate<='2009-04-22'" I have to take an extra moment to make sure the two fields are the same. Also, as a query gets edited over time, a sloppy programmer might separate the two fields. I've seen plenty of queries that say something like "where transactiondate>='2009-04-17' and salestype='A' and customernumber=customer.idnumber and transactiondate<='2009-04-22'". If they try this with a BETWEEN, of course, it will be a syntax error and promptly fixed.
Although BETWEEN is easy to read and maintain, I rarely recommend its use because it is a closed interval and as mentioned previously this can be a problem with dates - even without time components.
For example, when dealing with monthly data it is often common to compare dates BETWEEN first AND last, but in practice this is usually easier to write dt >= first AND dt < next-first (which also solves the time part issue) - since determining last usually is one step longer than determining next-first (by subtracting a day).
In addition, another gotcha is that lower and upper bounds do need to be specified in the correct order (i.e. BETWEEN low AND high).