Select BETWEEN column values

Question

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 1

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.