w3resource logo


>SQL Between operator

SQL BETWEEN Operator

rating Sql Between Operator has average rating 10 out of 10. Total 3 users rated.

<<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

Name Description
column_name Name of the column of the table.
expression Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.
table_name Name of the table.
value_from,value_to Starting value and ending value.

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 dualWHERE 10 BETWEEN 5 AND 20;

Output

Sql In operator using numeric value

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 In operator using numeric value

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 In

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_areaFROM customerWHERE cust_name BETWEEN 'A' AND 'I';

Output

Sql Between operator using text value

Pictorial presentation

Sql Between operator using text value

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_areaFROM customerWHERE cust_name NOT BETWEEN 'K' AND 'Y';

Output

Sql Between operator boolean Not

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_codeFROM ordersWHERE ord_date NOT BETWEEN '15-Feb-08' AND '30-Jul-08';

Output

Sql Between operator with Not and date value

SQL Online Practice Editor :


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

See our Model Database



<<PreviousNext>>