DEV Community

Mohamed Mayallo
Mohamed Mayallo

Posted on • Originally published at mayallo.com

Is SQL BETWEEN Inclusive or Exclusive?

#database #postgres #sql

TL;DR

The SQL BETWEEN operator is inclusive. That means both the lower and upper boundary values are included in the results.

Introduction

When writing SQL queries, many developers wonder: is SQL BETWEEN inclusive or exclusive? The answer is simple: BETWEEN is inclusive. This means that if you write BETWEEN 10 AND 20, both 10 and 20 are part of the results.

SQL BETWEEN Example

Imagine we have a table called products:

id name price
1 Pen 5
2 Notebook 10
3 Bag 20
4 Laptop 50 

SELECT * 
FROM products
WHERE price BETWEEN 10 AND 20;

Result:

id name price
2 Notebook 10
3 Bag 20

Explanation:
Both 10 and 20 are included. That’s why we say SQL BETWEEN is inclusive.

SQL BETWEEN vs Greater Than and Less Than


SELECT * 
FROM products
WHERE price >= 10 AND price <= 20;

This gives the same result as using BETWEEN.

id name price
2 Notebook 10
3 Bag 20

So, BETWEEN = >= and <=.

Common Misunderstanding

Some developers think that BETWEEN is exclusive (ignores boundaries). That’s wrong. If you wanted to exclude the edges, you would use > and < instead: 


SELECT * 
FROM products
WHERE price > 10 AND price < 20;

Result:

id name price
-- -- --

Here no rows match because only a price between 10 and 20 (like 15) would qualify, but our table doesn’t have that value.

Conclusion

So, is SQL BETWEEN inclusive or exclusive? The answer: SQL BETWEEN is inclusive.

  • It includes both boundary values.
  • It works the same as >= and <=.
  • If you need exclusive ranges, use > and <.

This makes BETWEEN a very convenient way to filter data in an inclusive range.

Finally, if you found this article helpful, you can check more here:

Top comments (0)