How is the SQL Between Operator function

The BETWEEN operator allows us to select values within a range.

Syntax :

SELECT (column name) FROM table_name
 WHERE (column name) BETWEEN 'value1' AND 'value2';
This will select all rows whose column has a value between 'value1' and 'value2'. Let's create the table we will use for our examples:

Script:

--first lets drop the table Test (if she exists, if not skip the drop line).
Drop table test;
create table test (id int,name varchar(20),email varchar(20),salary int ,de char(20));
--insert values into table
insert into test values (1,'Eve','[email protected]',1500,'HR');
insert into test values (2,'Jon','[email protected]',2500,'AD');
insert into test values (3,'Mike','[email protected]',3000,'AD');
insert into test values (4,'Paul','[email protected]',3200,'HR');
insert into test values (5,'Mary','[email protected]',1800,'IT');
insert into test values (6,'Jane','[email protected]',2200,'IT');
Check the table to see the data and the structure. 
SQLselect * from test ;
    ID NAME         EMAIL            SALARY  DE
---------- -------------------- -------------------- ---------- --  --------
     1  Eve         [email protected]       1500   HR
     2  Jon         [email protected]       2500   AD
     3  Mike       [email protected]       3000   AD
     4  Paul        [email protected]       3200   HR
     5  Mary       [email protected]       1800   IT
     6  Jane        [email protected]      2200   IT
Very good

Example 1:

  • say you want to get all the names that have ID values between 1 and 3.
Note: depending on the database you use the names that carry the values used in the condition may be listed or not, In our case they appear. 
SQLselect name from test where id between 1 and 3;
NAME
--------------------
Eve
Jon
Mike
-this is when we use a numeric value.

Example 2 :

-now we will use string values: 
SQLselect name from test where name between 'Eve' and 'Mike';
NAME
--------------------
Eve
Jon
Mike
Mary
Jane
  • see that we have 5 names listed and 6 names in our table
How does the BETWEEN operator works with strings ? -well he will chose all the names that are between alphabetically.
  • so we see that Eve=E and Mike=M all names that are found in a,b,c,e...---... m...z will be selected.
-for use to understand more this concept we will create table that will have the alphabet letters, not all of them but in the same alphabetical order.

Here is the script

Create table aaa( letter char(10));
Insert into aaa values('a');
Insert into aaa values('b');
Insert into aaa values('c');
Insert into aaa values('d');
Insert into aaa values('e');
Insert into aaa values('f');
Insert into aaa values('g');
Insert into aaa values('h');
Ok now , after creating the table let's use the between operator.

Example :

SQLSelect * from aaa
    where leter between 'a' and 'd';Letter-ab cd
-we see in this query brings all the letters from a-b;

Example :

SQLSelect * from aaa
   where leter between 'a' and 'x';
   Letter
   --------------------
   a
   b
   c
   d
   e
   f
   g
   h
  • now in this case thou we do not have all the letter that might be between a-x the query will bring all the letter that GO between the declared values , in our case a-x;
We can also use date as values in our between operators.

Copyright 2020. All Rights Reserved