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 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

回答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.

回答2:

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

回答3:

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)

回答4:

(column1 <= column2 AND column1 >= column3)

These two conditions only yield TRUE if the three values are ordered as follows:

column3 <= column1 <= column2

That is, the condition is equivalent to:

column1 BETWEEN column3 AND column2

---

Likewise,

(column1 >= column2 AND column1 <= column3)

implies this ordering:

column2 <= column1 <= column3

That is, the condition is equivalent to your first WHERE clause, ie.:

column1 BETWEEN column2 AND column3

---

In both cases, column1 is the middle value for all query results, as you have already noticed.

And the two queries do yield different results because the first has a WHERE clause of:

column1 BETWEEN column2 AND column3

whereas the second query effectively has a WHERE clause of:

(column1 BETWEEN column2 AND column3) OR (column1 BETWEEN column3 AND column2)

(Which means that the order of the two right operands of the BETWEEN .. AND .. operator is actually important.)