Select BETWEEN column values

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 some开发者_运维知识库thing 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

---

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.

---

Between A And B assumes that A<B, i.e., that the first expression in the Between, (A), is less than the second expression, (B) it does not check or execute with the opposite option.

e.g., if you put Where 3 Between 4 And 2 no rows will be returned:

or, if you write

Select Case When 3 Between 4 and 2 then 'true' else 'false' end

it will return false

---

Your logic for the two statements is not the same:

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

Has two clauses. Remove the first and you should have the same results as your between statement.

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