SQL BETWEEN Operator
BETWEEN Operator
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 TRUE when the search value present within the range otherwise returns FALSE. The results are NULL if any of the range values are NULL.
Syntax:
SELECT [column_name... | expression1 ] FROM [table_name] WHERE expression2 [NOT] BETWEEN value_from AND value_to;
Parameters:
Name | Description |
---|---|
column_name | Name of the column of the table. |
expression1 | 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. |
WHERE expression2 | Compares a scalar expression, such as a column, to the range of values bounded by value_from and value_to |
value_from, value_to | Starting value and ending value. |
DBMS Support: BETWEEN Operator
DBMS | Command |
MySQL | Supported |
PostgreSQL | Supported |
SQL Server | Supported |
Oracle | Supported |
Example: SQL BETWEEN Operator
To know whether the search value 15 is present within the specified range from the DUAL table, the following SQL statement can be used:
SQL Code:
SELECT 'found'
FROM dual
WHERE 10 BETWEEN 5 AND 20;
Output:
'FOUND' -------- found
Relational Algebra Expression:
Relational Algebra Tree:
Pictorial Presentation : SQL BETWEEN operator
Here we look for all agents in the agents table of inventory database whose commission should be within .12 to .14.
Sample table: agents
SQL Code:
SELECT * FROM agents
WHERE commission BETWEEN .12 AND .14;
Output:
AGENT_CODE AGENT_NAME WORKING_AREA COMMISSION PHONE_NO COUNTRY ---------- -------------------- -------------------- ---------- --------------- ---------- A003 Alex London .13 075-12458969 A001 Subbarao Bangalore .14 077-12346674 A008 Alford New York .12 044-25874365 A010 Santakumar Chennai .14 007-22388644 A012 Lucida San Jose .12 044-52981425 A005 Anderson Brisban .13 045-21447739
Pictorial Presentation: SQL BETWEEN operator
Example: SQL Between operator with IN
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 :
SQL Code:
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:
AGENT_CODE CUST_CODE CUST_NAME CUST_CITY ---------- ---------- ---------------------------------------- ------------ A003 C00013 Holmes London A008 C00001 Micheal New York A008 C00020 Albert New York A006 C00024 Cook London A003 C00015 Stuart London A008 C00002 Bolt New York A006 C00023 Karl London
Example: SQL Between operator with text value
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 begin with any of the letters between 'A' and 'I',
the following SQL statement can be used :
SQL Code:
SELECT cust_code, cust_name, cust_city, working_area
FROM customer
WHERE cust_name BETWEEN 'A' AND 'I';
Output:
CUST_CODE CUST_NAME CUST_CITY WORKING_AREA ---------- --------------------- ----------------------------------- -------------- C00013 Holmes London London C00020 Albert New York New York C00024 Cook London London C00002 Bolt New York New York C00018 Fleming Brisban Brisban C00022 Avinash Mumbai Mumbai C00010 Charles Hampshair Hampshair
Relational Algebra Expression:
Relational Algebra Tree:
Example: SQL Between operator with boolean NOT
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 letters 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
CUST_CODE CUST_NAME CUST_CITY WORKING_AREA ---------- ------------------------------ ----------------------------------- ------------- C00013 Holmes London London C00020 Albert New York New York C00024 Cook London London C00002 Bolt New York New York C00018 Fleming Brisban Brisban C00021 Jacks Brisban Brisban C00019 Yearannaidu Chennai Chennai C00022 Avinash Mumbai Mumbai C00010 Charles Hampshair Hampshair
Relational Algebra Expression:
Relational Algebra Tree:
Example:SQL Between operator with NOT on date value
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 :
SQL Code:
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:
ORD_NUM ORD_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ---------- ---------- --------- ---------- ---------- 200114 3500 15-AUG-08 C00002 A008 200122 2500 16-SEP-08 C00003 A004 200119 4000 16-SEP-08 C00007 A010 200121 1500 23-SEP-08 C00008 A004 200134 4200 25-SEP-08 C00004 A005 200125 2000 10-OCT-08 C00018 A005 200117 800 20-OCT-08 C00014 A001 200123 500 16-SEP-08 C00022 A002 200135 2000 16-SEP-08 C00007 A010 200131 900 26-AUG-08 C00012 A012 200100 1000 08-JAN-08 C00015 A003 200107 4500 30-AUG-08 C00007 A010
Relational Algebra Expression:
Relational Algebra Tree:
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework