Difference between BETWEEN and AND query?

[jainbrijesh]

i write a query

select * userid from users where userid between 1000 and 3000;

now i write another query

select * userid from users where userid > 1000 and userid <3000;

What is the difference b/w both query?

[Barbie]

No difference at all. between and is the alternative for your second query.

[jainbrijesh]

No difference at all. between and is the alternative for your second query.

Barbie you say there is no difference but when i executed these queries i got different results.
theoretically there is no difference , but practically we get different query result, what a joke it is ?

[rishamjeet]

Hi

There is difference between the two queries.

wen u use BETWEEN, both the values are inclusive in the search criteria.
whereas this is not the case in your second query.
for both the queries to return same results, use >= and <= instead of > and <

[jainbrijesh]

Hi

There is difference between the two queries.

wen u use BETWEEN, both the values are inclusive in the search criteria.
whereas this is not the case in your second query.
for both the queries to return same results, use >= and <= instead of > and <

Yes friend,
You are right.
Same happens when we do it practically.

[Barbie]

yeah. its right. i am sorry.

[reetasharma108]

Hi, you can see the difference with following example:- sql> select * from student1; name dept salary ---------- ---------- ---------- reeta dm 10000 reeta dm 20000 reeta dm 30000 reeta dm 400000 sql> select * from student1 where salary between 10000 and 30000; name dept salary ---------- ---------- ---------- reeta dm 10000 reeta dm 20000 reeta dm 30000 sql> select * from student1 where salary>10000 and salary<30000; name dept salary ---------- ---------- ---------- reeta dm 20000 it means between function give us back the 10000 and 30000 values also rather than on 20000 which is greater than 10000 but less than 30000; i hope this work for you reeta

[sutnarcha]

Another difference is, as BETWEEN-AND pair is a SQL clause of the command it works faster.

If a combination of comparison and logical operators are used in a query, the query runner will have to construct the query evaluation program and then apply the given criteria and boundaries in the evaluation program. Where as if a BETWEEN-AND pair is used, it has to only apply boundaries and extract rows from the tale.

[debasisdas]

dear friends

BETWEEN is a range operator .which is used to check for a range of values. recomended to use only with number and date datatype.

AND is a logical operator, which is used to logically concadinate more than one conditions.

[soorajsk_84]

Between- It returns data between two sets of values
eg:between 10 AND 20 mean returns data between 10 and 20 inclucing 10 and 20
AND:and returns value when two conditions are satisfied