SQL Between operator

<<PreviousNext>>

Description

The SQL BETWEEN operator tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression. The operator returns 1 when the search value present within the range otherwise returns 0.

Syntax

SELECT [column_name... | expression ]
FROM [table_name]
WHERE [NOT] BETWEEN value_from AND value_to;

Parameters

Example

To know whether the search value 15 is present within the specified range from the DUAL table, the following sql statement can be used :

SELECT 'found'
FROM dual
WHERE 10 BETWEEN 5 AND 20;

Output

Sample table : agents

To get data of all columns from the 'agents' table with following condition -

1. percentage of commission for the 'agents' should be within .12 to .14,

the following sql statement can be used :

SELECT * FROM agents 
WHERE commission BETWEEN .12 AND .14;
  

Output

SQL Between operator with IN

Description

In the following we are discussing the usage of SQL BETWEEN operator using IN operator in the select statement.

Example

Sample table : customer

To get data of all columns from the 'customer' table with following conditions -

1. 'agent_code' must be within 'A003' and 'A008',

2. but 'agent_code' 'A004', 'A007' and 'A005' should not appear,

the following sql statement can be used :

SELECT agent_code, cust_code, cust_name, cust_city 
FROM customer  

WHERE (agent_code BETWEEN 'A003' AND 'A008') 
AND NOT agent_code IN ('A004','A007','A005');

Output

SQL Between operator with text value

Description

In the following we are discussing the usage of SQL BETWEEN operator using text value in the select statement.

Example

Sample table : customer

To get data of 'cust_code', 'cust_name', 'cust_city', 'working_area' columns from the 'customer' table with following condition -

1. ' cust_name' must be begin with any of the letter between 'A' and 'I',

the following sql statement can be used :

SELECT cust_code, cust_name, cust_city, working_area
FROM customer
WHERE cust_name BETWEEN 'A' AND 'I';

Output

Pictorial presentation

SQL Between operator with boolean NOT

Description

In the following we are discussing the usage of SQL BETWEEN operator using boolean 'NOT' operator in a select statement.

Example

Sample table :customer

To get data of 'cust_code', 'cust_name', 'cust_city', 'working_area' columns from the 'customer' table with following condition -

1. 'cust_name' must not begin with any of the letter  between 'K' and 'Y' ,

the following sql statement can be used :

SELECT cust_code, cust_name, cust_city, working_area
FROM customer
WHERE cust_name NOT BETWEEN 'K' AND 'Y';

Output

SQL Between operator with NOT on date value

Description

In the following we are discussing the usage of SQL BETWEEN operator using boolean 'NOT' operator on date value in a select statement.

Example

Sample table : orders

To get data of 'ord_num',  'ord_amount',  'advance_amount',  'ord_date', 'cust_code' and  'agent_code' from the 'orders' table with following condition -

1. 'ord_date' must be a date before '15-Feb-08' or after '30-Jul-08',

the following sql statement can be used :

SELECT ord_num, ord_amount, ord_date, cust_code, agent_code
FROM orders
WHERE ord_date NOT BETWEEN '15-Feb-08' AND '30-Jul-08';

Output

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

See our Model Database