mysql query . need assistance

Question

say if the table trap has column 'id' and rows contents as

7
8
9
10
11
12
13
14

---

SELECT id FROM trap
WHERE id<10 and id>12

this doesn't give any output

but if

SELECT id FROM trap
WHERE id>7 and id<14

give me the required output i.e.,

8
9
10
11
12
13

Answer 1

I think you're mistaken, or you've phrased the question wrong.

It's impossible for id < 10 and id > 12 to be true at the same time. That's why you aren't getting any results for your first query.

What you're looking for is:

SELECT id FROM trap WHERE id<10 OR id>12

Answer 2

The problem is that you're using the AND operator where you should be using OR.

SELECT id FROM trap WHERE id<10 OR id>12

Obviously id cannot be both 10 AND 12 at the same time, one box can only hold one value.

Alternatively you can write the statement as:

SELECT id FROM trap WHERE NOT(id BETWEEN 10 AND 12) 

The reason that SELECT id FROM trap WHERE id>7 and id<14 does work is that it is possible for a value to be BETWEEN 8 AND 13 (inclusive) at the same time.
However no way can a value ever the smaller than 10 and larger than 12 at the same time.

So if the conditions are mutually exclusive you must use OR, if the conditions do not exclude one and other then you must use AND.
You can wrap a test inside a NOT() to reverse the test, this is because AND and OR are exact opposites.