SQL: how to omitt “=”, using only BETWEEN operator

215
November 24, 2017, at 09:29 AM

We have e.g. next table 'Persons':

Name    Salary    
Jack    500
Joe     1000
Nancy   550
Fiby    700
Max     1100

I want to select persons with salary between borders (500, 1000), using only BETWEEN operator, and selected salaries should be > 500, and < 1000. BETWEEN is shortage of >=, <=, so how to omitt that "="?

(yep, it can be solved e.g. with

SELECT * FROM Persons
WHERE Salary BETWEEN 500 and 1000
AND Salary NOT IN (500, 1000);

But just wondering - is it possible to do such selection with using Only BETWEEN (and 500, 1000 numbers).

Answer 1

You cant. BETWEEN include border cases that is how work. Is like you ask a clock cook some pancakes, isn't what they are build for.

And you already have a workaround. Another would be:

 WHERE Salary > 500 and Salary < 1000
Answer 2

If the datatype is INT, just adjust the range by 1.

WHERE salary BETWEEN 501 AND 999
Rent Charter Buses Company
READ ALSO
Merging 2 tables preserving the ID

Merging 2 tables preserving the ID

I have a question about merging a table with another preserving an ID on a database (I'm using MySQL)I have 2 tables, the first has and Item ID and a category and subcategory assigned to that ID

226
Query to obtain the lowest price per room per hotel in a relational database

Query to obtain the lowest price per room per hotel in a relational database

I have a relational database model based on a hotel booking site set up for a uni project, however I am stumped by one query

150
Complex split comma value in MySQL

Complex split comma value in MySQL

below is my problem

216
SQL for aggregating customers by country

SQL for aggregating customers by country

I am trying to write some SQL to aggregate (count) how many customers I have per countrySo far I have made the easy query to count how many users there are in a specific country

209