Understanding MySQL BETWEEN Condition

No votes yet.
Please wait...

Hello guys,
PHP HURDLES is once again here with yet another intersting and commonly asked topic  Understanding SQL BETWEEN Condition in PHP .

The SQL BETWEEN Condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.The values can be numbers, text, or dates.

BETWEEN Condition is used with WHERE clause to find the actual result between a range.

Syntax

1
2
3
SELECT column_name(s)
FROM table_name
WHERE column_name </em>BETWEEN value1 AND value2;

The SQL BETWEEN Condition will return the records where expression is within the range of value1 and value2 (inclusive).

Let us understand the condition with some examples

Example with DATE Values

let’s see  how we can use the BETWEEN condition with Dates. The following date example uses the SQL BETWEEN condition to retrieve values within a date range.

1
2
3
4
SELECT *
FROM birth_dates
WHERE date_of_birth BETWEEN TO_DATE ('2000/01/01', 'yyyy/mm/dd')
AND TO_DATE ('2017/12/01', 'yyyy/mm/dd');

This is the  SQL BETWEEN condition example which will return all records where the date_of_birth is between Jan 1, 2000 and Dec 01, 2017 (inclusive).

Example With Numeric Values

This is the  numeric example uses the BETWEEN condition to retrieve values within a numeric range.

1
2
3
SELECT *
FROM members
WHERE member_id BETWEEN 1 AND 10 ;

This SQL Query will return all rows where the member_id is between 1 and 10 (inclusive).

Example Using Not Operator

The SQL BETWEEN condition can also be combined with the SQL NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator.

1
2
3
SELECT *
FROM members
WHERE member_id NOT BETWEEN 1 AND 1o;

This Query will return return all rows where the member_id is NOT between 1 and 10, inclusive.

Note 

  •  BETWEEN returns values that fall within a given range.
  •  BETWEEN is a shorthand for >= AND <=.
  • BETWEEN condition is inclusive: begin and end values are included.

So Guys this is it from this tutorial which is all in all a very helpful tutorial for developers who are new in PHP or web development.
For more tutorials of your choice please comment us or you can also mail us.
You can also read our tutorials

UNDERSTANDING SQL COMPARISON OPERATORS
Preventing SQL Injection In PHP
Because Hurdles aren’t really Hurdles

2 Comments

  1. Raman said:

    Are Camprison operators >, >=, <, <= fetch data faster then BETWEEN operator?

    No votes yet.
    Please wait...
    August 9, 2017
    Reply
    • Saurabh Dubey said:

      No Raman the speed for both of them are equal it’s just that BETWEEN is not supported on all RDBMS

      No votes yet.
      Please wait...
      August 9, 2017
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *