x

SQL – BETWEEN AND & NOT BETWEEN AND

Prev     Next

BETWEEN…AND operator in SQL are used to select in-between values from the given range/ values. It is used in a WHERE clause in SELECT, UPDATE and DELETE statements/queries. Syntax for SQL BETWEEN…AND operators are given below.


Syntax for BETWEEN…AND operator in SQL:

Syntax for SQL BETWEEN…AND operator SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 BETWEEN value1 AND value2;
Syntax for SQL NOT BETWEEN…AND operator SELECT column_name1, column_name2, etc
FROM table_name
WHERE  column_name1 NOT BETWEEN value1 AND value2;

Please consider the following table with few records as given below.

Table name (for example): student
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows

sql-table28


Example1: How to use BETWEEN…AND in a WHERE clause in SELECT queries

SQL query:

SELECT Student_ID, Student_name, City, Age from student
WHERE Age BETWEEN 19 AND 25;

SQL query Output:

sql-table30

Example2: How to use NOT BETWEEN…AND in WHERE clause in SELECT queries

SQL query:

SELECT Student_ID, Student_name, City, Age from student
WHERE Age NOT BETWEEN 19 AND 25;

SQL query Output:

sql-table29

Note:

  • Sometime, BETWEEN operators might not consider fetching the starting and ending values in the given range when we select. This behaves based on the databases we use like Oracle, MySQL etc. To check how it behaves, please test this query in different databases before you use.

Prev     Next



Like it? Please Spread the word!