w3resource

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'
-- Selecting the string 'found'
FROM dual
-- From the virtual table named "dual"
WHERE 10 BETWEEN 5 AND 20;
-- Where the value 10 is between 5 and 20

Explanation:

  • SELECT 'found': This specifies that we want to select the string 'found' as a result.
  • FROM dual: This indicates that we are selecting from the virtual table dual, which is a special one-row table present in some database systems like Oracle.
  • WHERE 10 BETWEEN 5 AND 20: This is a conditional clause that filters the rows returned by the query. It specifies that the value 10 is between 5 and 20.

Output:

'FOUND'
--------
found

Relational Algebra Expression:

Relational Algebra Expression: SQL BETWEEN Operator.

Relational Algebra Tree:

Relational Algebra Tree: SQL BETWEEN Operator

Visual Presentation : SQL BETWEEN operator

Example : 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
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

SQL Code:


SELECT * 
-- Selecting all columns (*) from the table
FROM agents 
-- From the table named "agents"
WHERE commission BETWEEN .12 AND .14;
-- Where the value in the column "commission" is between .12 and .14

Explanation:

  • SELECT *: This specifies that we want to retrieve all columns from the table.
  • FROM agents: This indicates the table from which we want to retrieve the data. In this case, the table is named "agents".
  • WHERE commission BETWEEN .12 AND .14: This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "commission" column is between .12 and .14 should be included in the result set.

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

Visual Presentation: SQL BETWEEN operator

SQL BETWEEN operator, range test

Example: SQL Between operator with IN

Sample table: customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

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 
-- Selecting specific columns: agent_code, cust_code, cust_name, and cust_city 
FROM customer
-- From the table named "customer"
WHERE (agent_code BETWEEN 'A003' AND 'A008')
-- Where the value in the column "agent_code" is between 'A003' and 'A008' (inclusive)
AND NOT agent_code IN ('A004', 'A007', 'A005');
-- And the value in the column "agent_code" is not 'A004', 'A007', or 'A005'

Explanation:

  • SELECT agent_code, cust_code, cust_name, cust_city: This specifies that we want to retrieve specific columns (agent_code, cust_code, cust_name, and cust_city) from the table.
  • FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".
  • WHERE (agent_code BETWEEN 'A003' AND 'A008'): This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "agent_code" column is between 'A003' and 'A008' (inclusive) should be included in the result set.
  • AND NOT agent_code IN ('A004', 'A007', 'A005'): This further narrows down the results by excluding rows where the value in the "agent_code" column is 'A004', 'A007', or '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
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

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
-- Selecting specific columns: cust_code, cust_name, cust_city, and working_area
FROM customer
-- From the table named "customer"
WHERE cust_name BETWEEN 'A' AND 'I';
-- Where the value in the column "cust_name" is between 'A' and 'I' (inclusive)

Explanation:

  • SELECT cust_code, cust_name, cust_city, working_area: This specifies that we want to retrieve specific columns (cust_code, cust_name, cust_city, and working_area) from the table.
  • FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".
  • WHERE cust_name BETWEEN 'A' AND 'I': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "cust_name" column falls between 'A' and 'I' (inclusive) should be included in the result set.

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 Expression: SQL Between operator with text value.

Relational Algebra Tree:

Relational Algebra Tree: SQL Between operator with text value

Example: SQL Between operator with boolean NOT

Sample table: customer
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
|CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
| C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
| C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
| C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
| C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
| C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
| C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
| C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
| C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
| C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
| C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
| C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
| C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
| C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
| C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
| C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
| C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
| C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
| C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
| C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
| C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
| C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
| C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
| C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
| C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
| C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
+-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+

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
-- Selecting specific columns: cust_code, cust_name, cust_city, and working_area
FROM customer
-- From the table named "customer"
WHERE cust_name NOT BETWEEN 'K' AND 'Y';
-- Where the value in the column "cust_name" is not between 'K' and 'Y' (inclusive)

Explanation:

  • SELECT cust_code, cust_name, cust_city, working_area: This specifies that we want to retrieve specific columns (cust_code, cust_name, cust_city, and working_area) from the table.
  • FROM customer: This indicates the table from which we want to retrieve the data. In this case, the table is named "customer".
  • WHERE cust_name NOT BETWEEN 'K' AND 'Y': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "cust_name" column is not between 'K' and 'Y' (inclusive) should be included in the result set.

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 Expression: SQL Between  operator with boolean NOT.

Relational Algebra Tree:

Relational Algebra Tree: SQL Between  operator with boolean NOT.

Example:SQL Between operator with NOT on date value

Sample table: orders
   ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
    200114       3500           2000 15-AUG-08 C00002          A008
    200122       2500            400 16-SEP-08 C00003          A004
    200118        500            100 20-JUL-08 C00023          A006
    200119       4000            700 16-SEP-08 C00007          A010
    200121       1500            600 23-SEP-08 C00008          A004
    200130       2500            400 30-JUL-08 C00025          A011
    200134       4200           1800 25-SEP-08 C00004          A005
    200108       4000            600 15-FEB-08 C00008          A004
    200103       1500            700 15-MAY-08 C00021          A005
    200105       2500            500 18-JUL-08 C00025          A011
    200109       3500            800 30-JUL-08 C00011          A010
    200101       3000           1000 15-JUL-08 C00001          A008
    200111       1000            300 10-JUL-08 C00020          A008
    200104       1500            500 13-MAR-08 C00006          A004
    200106       2500            700 20-APR-08 C00005          A002
    200125       2000            600 10-OCT-08 C00018          A005
    200117        800            200 20-OCT-08 C00014          A001
    200123        500            100 16-SEP-08 C00022          A002
    200120        500            100 20-JUL-08 C00009          A002
    200116        500            100 13-JUL-08 C00010          A009
    200124        500            100 20-JUN-08 C00017          A007
    200126        500            100 24-JUN-08 C00022          A002
    200129       2500            500 20-JUL-08 C00024          A006
    200127       2500            400 20-JUL-08 C00015          A003
    200128       3500           1500 20-JUL-08 C00009          A002
    200135       2000            800 16-SEP-08 C00007          A010
    200131        900            150 26-AUG-08 C00012          A012
    200133       1200            400 29-JUN-08 C00009          A002
    200100       1000            600 08-JAN-08 C00015          A003
    200110       3000            500 15-APR-08 C00019          A010
    200107       4500            900 30-AUG-08 C00007          A010
    200112       2000            400 30-MAY-08 C00016          A007
    200113       4000            600 10-JUN-08 C00022          A002
    200102       2000            300 25-MAY-08 C00012          A012

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 
-- Selecting specific columns: ord_num, ord_amount, ord_date, cust_code, and agent_code
FROM orders
-- From the table named "orders"
WHERE ord_date NOT BETWEEN '15-Feb-08' AND '30-Jul-08';
-- Where the value in the column "ord_date" is not between '15-Feb-08' and '30-Jul-08' (inclusive)

Explanation:

  • SELECT ord_num, ord_amount, ord_date, cust_code, agent_code: This specifies that we want to retrieve specific columns (ord_num, ord_amount, ord_date, cust_code, and agent_code) from the table.
  • FROM orders: This indicates the table from which we want to retrieve the data. In this case, the table is named "orders".
  • WHERE ord_date NOT BETWEEN '15-Feb-08' AND '30-Jul-08': This is a conditional clause that filters the rows returned by the query. It specifies that only rows where the value in the "ord_date" column is not between '15-Feb-08' and '30-Jul-08' (inclusive) should be included in the result set.

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 Expression: SQL Between  operator with boolean NOT.

Relational Algebra Tree:

Relational Algebra Tree: SQL Between  operator with boolean NOT.

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/sql/between-operator/sql-between-operator.php