SQL: Why is BETWEEN operator inclusive?

Question

My first post here. :)

My teacher asks me to: Write a query to display the names of those students that are between the ages of 18 and 20.

I write:

SELECT * FROM students WHERE age BETWEEN 18 AND 20;

I feel that:

SELECT * FROM students WHERE age = 19; --should be an equivalent statement

My expectation is that between 18 and 20 would be the age 19. But it includes 18 and 20. This seems crazy to me. Is there a reason that BETWEEN would include the upper and lower bound?

Is it normal for people in SQL to ask for something between but expect to include values?

Answer 1

Yes, BETWEEN is inclusive for both bounds as defined in ANSI SQL, and in all databases that I know.

Postgres is no exception:

The BETWEEN predicate simplifies range tests:

a BETWEEN x AND y

is equivalent to

a >= x AND a <= y

Chances are that your teacher does understand this the same way.

If you want a non-inclusive predicate, then you need to be explicit about it:

SELECT * FROM students WHERE age > 18 AND age < 20