SQL BETWEEN and NOT BETWEEN |
SQL Sample Code - SQL BETWEEN Operator. |
| SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; |
| SQL Sample Code - SQL NOT BETWEEN Operator. |
| SELECT column_name(s) FROM table_name WHERE column_name NOT BETWEEN value1 AND value2; |
See the SQL samples section below for an explanation of the Between and Not Between operators.
| What does the SQL BETWEEN operator do? |
The SQL BETWEEN operator is used as part of a WHERE clause to retrieve information between a range of two specified values.
If we use the BETWEEN operator alongside the NOT operator we can retrieve information that does not appear between the specified ranges.
| Sample 1 – SQL BETWEEN |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town | Sales |
| 1 | SQL Sample | 1 Sample St | Hamburg | 10000 |
| 2 | SQL Code Land | 2 Code Rd | Hamburg | 20000 |
| 3 | Sample Code World | 66 SQL St | Curry | 18000 |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa | 17000 |
In this SQL sample we want to retrieve all the columns from the tblCompany table whose Sales figures are between 10000 and 19000.
Use the SELECT statement below:
| SELECT * FROM tblCompany WHERE Sales BETWEEN ‘10000’ AND ‘19000’ |
The table of results will look like this:
| Company_ID | CompanyName | Address | Town | Sales |
| 1 | SQL Sample | 1 Sample St | Hamburg | 10000 |
| 3 | Sample Code World | 66 SQL St | Curry | 18000 |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa | 17000 |
| Sample 2 – SQL NOT BETWEEN |
In this SQL sample we will use the database table ‘tblCompany’.
| Company_ID | CompanyName | Address | Town | Sales |
| 1 | SQL Sample | 1 Sample St | Hamburg | 10000 |
| 2 | SQL Code Land | 2 Code Rd | Hamburg | 20000 |
| 3 | Sample Code World | 66 SQL St | Curry | 18000 |
| 4 | SQL Reference Ltd | 34 Reference St | Pisa | 17000 |
In this SQL sample we want to retrieve all the columns from the tblCompany table whose Sales figures are NOT between 10000 and 19000.
Use the SELECT statement below:
| SELECT * FROM tblCompany WHERE Sales NOT BETWEEN ‘10000’ AND ‘19000’ |
The table of results will look like this:
| Company_ID | CompanyName | Address | Town | Sales |
| 2 | SQL Code Land | 2 Code Rd | Hamburg | 20000 |
Please note: Depending upon which database you are using, (MySQL, Oracle, SQL Server etc) the BETWEEN operator will be interpreted differently. Certain databases would not have selected the Burger Town record of 10000 in Example 1 as the BETWEEN statement is interpreted as between and not including 10000. Consult your databases guidance for interpretations.
Related SQL Sample Code:
|
|