How to use SQL Between Operator

The general syntax of the between logical operator is:

Select * from table_name

Where col_name BETWEEN val1 AND val2

Where val1 and val2 can be two numbers, characters or dates.

See a between example
Between with dates example

The Between operator

A few main points about the SQL Between operator are:

  • The between is a logical operator that is used in the Where clause to specify a condition.
  • The between operator takes two values: minimum and maximum values and search between the two including the min and max values.
  • The values in the between operator can be two numbers, two dates or two characters etc.
  • e.g. between 10 AND 20; between a AND e; between 1/1/2015 AND 1/6/2015 etc.
  • The Between operator simplifies two AND conditions into one (explained later part of this tutorial)
  • The between SQL operator is used with the Where clause.

Examples of between operator

Let us show you examples of between operator with our created database and table.

We have a table, tbl_employee to show a few examples of the SQL between operator. The table structure is the same as we created in the Create table chapter and as shown below.

Example of between operator with numbers

This example shows how to use numbers in the between operator. We will run the SQL query on tbl_employee to return all records where column emp_id values fall between 2 and 4.

The query with between operator:

See Demo Table with between online

An example of between operator with text

This example shows how to use text in the between operator. The query will return all employees data whom names start with the letter in between A and M.

The between query:

See Demo Table with between online

Between with dates example

This example shows how to use dates with the between operator. We will run SQL query on tbl_employee to return all employees data whom joining date is between the given dates:

The between with dates query:

Demo table with between using dates

How Between operator simplifies two conditional statements

The between operator simplifies two AND conditions. However, it varies from database to database. Few databases include the given values (min and max) given in the between operator while other do not.

To elaborate further, how between simplifies the two AND conditions see the example below:

The SQL query with two conditional statements:

Same query with between operator:

Both of the above queries will produce the same output as shown in the example online (click the link below).

See Demo tables with Between and ‘AND’

As you can see between produces result just like two AND conditions.