BETWEEN

SQL BETWEEN Keyword

The BETWEEN keyword in SQL is used in a WHERE clause to filter the result set within a range. This range can consist of numbers, text, or dates, and is inclusive—meaning the start and end values are included in the result.

SQL BETWEEN Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT column1, column2, ...

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

To exclude values within the range, use:

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

SELECT column1, column2, ...

FROM table_name

WHERE column_name NOT BETWEEN value1 AND value2;

SQL BETWEEN Example with Patients Table (Numeric Range)

This query returns all patients with patient_id values from 2 to 5, including both endpoints.

SELECT *
FROM Patients
WHERE patient_id BETWEEN 2 AND 5;

SELECT *

FROM Patients

WHERE patient_id BETWEEN 2 AND 5;

SQL NOT BETWEEN Example with Prescriptions Table (Text Range)

The following SQL statement selects all prescriptions where the medication_name is not alphabetically between 'Amoxicillin' and 'Ibuprofen, This query returns all medications outside the alphabetic range from 'Amoxicillin' to 'Ibuprofen'.

SELECT *
FROM Prescriptions
WHERE medication_name NOT BETWEEN 'Amoxicillin' AND 'Ibuprofen';

SELECT *

FROM Prescriptions

WHERE medication_name NOT BETWEEN 'Amoxicillin' AND 'Ibuprofen';

SQL BETWEEN Example with Appointments Table (Date Range)

This query will return all appointment records with a date and time from July 20, 2024 to July 25, 2024, including those dates.

SELECT *
FROM Appointments
WHERE appointment_date BETWEEN '2024-07-20' AND '2024-07-25';

SELECT *

FROM Appointments

WHERE appointment_date BETWEEN '2024-07-20' AND '2024-07-25';

SQL BETWEEN Labs