SQL BETWEEN Operator

BETWEEN operator is used to getting the value within a given range. It is applicable in CRUD (create/read/update/delete) operation.

It is used between numeric range value
It is used between date range value
It is used between string range value

For SELECT Statement

Syntax:-


SELECT column_name(s) 
FROM table_name 
WHERE column_name range_value1 BETWEEN value2

Example:- Suppose we have employees table which has 7 records


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  1 | John       | Tailor    | john@abc.com   | USA       |
|  2 | Rom        | Tailor    | rom@abc.com    | USA       |
|  3 | Andrew     | Symonds   | andrew@abc.com | Australia |
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
|  7 | rohit      | NULL      | rohit@abc.com  | India     |

Find the employees details that have id value between 4 to 6.

Query:-


SELECT * FROM employees WHERE id between 4 AND 6;

Output:-


+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email          | country   |
+----+------------+-----------+----------------+-----------+
|  4 | Miacle     | Tailor    | miacle@abc.com | Australia |
|  5 | Sachin     | Tendulkar | sachin@abc.com | India     |
|  6 | Virat      | Kohli     | virat@abc.com  | India     |
+----+------------+-----------+----------------+-----------+
3 rows in set (0.00 sec)

SQL Distinct Clause – Interview Questions

Q 1: What is BETWEEN used for?

Ans: It selects values within a range.

Q 2: Is BETWEEN inclusive?

Ans: Yes, it includes boundary values.

Q 3: Can BETWEEN be used with dates?

Ans: Yes.

Q 4: Can NOT be used with BETWEEN?

Ans: Yes, as NOT BETWEEN.

Q 5: Is BETWEEN better than multiple conditions?

Ans: Yes, it simplifies queries.