SQL : BETWEEN vs = and =
They are identical: BETWEEN
is a shorthand for the longer syntax in the question that includes both values (EventDate >= '10/15/2009' and EventDate <= '10/19/2009'
).
Use an alternative longer syntax where BETWEEN
doesn't work because one or both of the values should not be included e.g.
Select EventId,EventName from EventMaster
where EventDate >= '10/15/2009' and EventDate < '10/19/2009'
(Note <
rather than <=
in second condition.)
BETWEEN clause versus = AND =
There is no performance difference between the two example queries because BETWEEN
is simply a shorthand way of expressing an inclusive range comparison. When Oracle parses the BETWEEN
condition it will automatically expand out into separate comparison clauses:
ex.
SELECT *
FROM table
WHERE column BETWEEN :lower_bound AND :upper_bound
...will automatically become:
SELECT *
FROM table
WHERE :lower_bound <= column
AND :upper_bound >= column
BETWEEN operator vs. = AND =: Is there a performance difference?
No benefit, just a syntax sugar.
By using the BETWEEN
version, you can avoid function reevaluation in some cases.
SQL Between clause with strings columns
The expression
name between 'A' and 'B'
is equivalent to
name>='A' and name<='B'
So 'Argentina' is >='A' and <='B' and it satisfies the condition. But 'Bolivia' is NOT <='B'. 'Bolivia'>'B'. It doesn't just look at the first letter: it looks at the whole string. Which is surely the way it ought to be: if it didn't do this, there'd be no way to say that you wanted a range that included 'Smith' but not 'Smithers'.
To accomplish what you want, you could say:
substr(name,1,1) between 'A' and 'B'
or:
name like 'A%' or name like 'B%'
or:
name>='A' and name<'C'
Difference between IN and ANY operators in SQL
SQL>
SQL> -- Use the ANY operator in a WHERE clause to compare a value with any of the values in a list.
SQL>
SQL> -- You must place an =, <>, <, >, <=, or >= operator before ANY.
SQL> SELECT *
2 FROM employee
3 WHERE salary > ANY (2000, 3000, 4000);
For In Operator
SQL> -- Use the IN operator in a WHERE clause to compare a value with any of the values in a list.
SQL> SELECT *
2 FROM employee
3 WHERE salary IN (2000, 3000, 4000);
But with the IN operator you cannot use =, <>, <, >, <=, or >=
MySQL between clause not inclusive?
The field dob
probably has a time component.
To truncate it out:
select * from person
where CAST(dob AS DATE) between '2011-01-01' and '2011-01-31'
SQL between not inclusive
It is inclusive. You are comparing datetimes to dates. The second date is interpreted as midnight when the day starts.
One way to fix this is:
SELECT *
FROM Cases
WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01'
Another way to fix it is with explicit binary comparisons
SELECT *
FROM Cases
WHERE created_at >= '2013-05-01' AND created_at < '2013-05-02'
Aaron Bertrand has a long blog entry on dates (here), where he discusses this and other date issues.
MYSQL query between two timestamps
Try:
SELECT *
FROM eventList
WHERE `date` BETWEEN FROM_UNIXTIME(1364256001) AND FROM_UNIXTIME(1364342399)
Or
SELECT *
FROM eventList WHERE `date`
BETWEEN '2013-03-26 00:00:01' AND '2013-03-26 23:59:59'
Is there a combination of LIKE and IN in SQL?
There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.
Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:
Oracle:
WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0
SQL Server:
WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
The column you are querying must be full-text indexed.
Reference:
- Building Full-Text Search Applications with Oracle Text
- Understanding SQL Server Full-Text
Does MS SQL Server's between include the range boundaries?
The BETWEEN operator is inclusive.
From Books Online:
BETWEEN returns TRUE if the value of
test_expression is greater than or
equal to the value of begin_expression
and less than or equal to the value of
end_expression.
DateTime Caveat
NB: With DateTimes you have to be careful; if only a date is given the value is taken as of midnight on that day; to avoid missing times within your end date, or repeating the capture of the following day's data at midnight in multiple ranges, your end date should be 3 milliseconds before midnight on of day following your to date. 3 milliseconds because any less than this and the value will be rounded up to midnight the next day.
e.g. to get all values within June 2016 you'd need to run:
where myDateTime between '20160601' and DATEADD(millisecond, -3, '20160701')
i.e.
where myDateTime between '20160601 00:00:00.000' and '20160630 23:59:59.997'
datetime2 and datetimeoffset
Subtracting 3 ms from a date will leave you vulnerable to missing rows from the 3 ms window. The correct solution is also the simplest one:
where myDateTime >= '20160601' AND myDateTime < '20160701'
Related Topics
How to Insert a Unique Id into Each SQLite Row
Postgresql Prefix Wildcard for Full Text
Turn Off SQL Logging While Keeping Settings.Debug
How to Create an Index in Amazon Redshift
What Is the Equivalent of 'Go' in MySQL
MySQL Slow on First Query, Then Fast for Related Queries
"Microsoft.Ace.Oledb.12.0" Cannot Be Loaded In-Process on a 64-Bit SQL Server
Use Row Values as Columns in Postgresql
Entity Framework: How to Properly Handle Exceptions That Occur Due to SQL Constraints
How to Create a Blank/Hardcoded Column in a SQL Query
How to Remove Leading and Trailing Quotes in SQL Server
Where Col1,Col2 in (...) [SQL Subquery Using Composite Primary Key]
SQL How to Select the Most Recent Date Item
Is 20 SQL Queries Per Page Load Really Considered a Lot
Select Multiple (Non-Aggregate Function) Columns with Group By