Select BETWEEN column values

sql between
Gonçalo Queirós picture Gonçalo Queirós · Sep 19, 2010 · Viewed 34.6k times · Source

I'm trying to use the BETWEEN with column names instead of direct values, something like this:

SELECT * FROM table WHERE column1 BETWEEN column2 AND column3;

This is returning something like 17 rows, but if i write:

SELECT * FROM table WHERE (column1 <= column2 AND column1 >= column3) OR (column1 >= column2 AND column1 <= column3)

i get around 600 rows.. In both cases i only get rows where column1 value is actually the middle value, but 2nd method gives me much more results, so 1st method has something wrong with it.

I suspect the problem might be on using BETWEEN clause with column names, instead of pure values, and somehow SQL is converting the column names to actual values..its strange, but can someone enlighten me please? Thanks

Answer

codaddict picture codaddict · Sep 19, 2010 
SELECT * FROM table WHERE column1 BETWEEN column2 AND column3; # gives 17 rows

is same as 

SELECT * FROM table WHERE (column1 >= column2 AND column1 <= column3) # gives 17 rows

Because of your addition check of

(column1 <= column2 AND column1 >= column3)

which is ORed, you get additional rows.

Related questions

Check if current date is between two dates Oracle SQL

I would like to select 1 if current date falls between 2 dates through Oracle SQL. I wrote an SQL after reading through other questions. https://stackoverflow.com/questions/2369222/oracle-date-between-query https://stackoverflow.com/questions/2399753/select-from-table-by-knowing-only-date-without-time-oracle But it returned only null. sysdate is …

Read More
Does MS SQL Server's "between" include the range boundaries?

For instance can SELECT foo FROM bar WHERE foo BETWEEN 5 AND 10 select 5 and 10 or they are excluded from the range?

Read More
Postgresql query between date ranges

I am trying to query my postgresql db to return results where a date is in certain month and year. In other words I would like all the values for a month-year. The only way i've been able to do …

Read More