BETWEEN & IN

The BETWEEN Operator

The BETWEEN operator is used to filter data within a certain range of values. This operator is inclusive — meaning both the lower and upper bounds are included.

BETWEEN Syntax

SELECT * FROM table_name
WHERE column BETWEEN lower_grade AND upper_grade;

BETWEEN Example

-- Employees with a salary between 4 million and 7 million
SELECT * FROM EMPLOYEE
WHERE SALARY BETWEEN 4000000 AND 7000000;

This query returns employees with a salary >= 4,000,000 AND <= 7,000,000.

NOT BETWEEN

Excludes a certain range of values:

-- Employees with a salary outside the 4-7 million range
SELECT * FROM EMPLOYEE
WHERE SALARY NOT BETWEEN 4000000 AND 7000000;

Returns employees with a salary < 4,000,000 or > 7,000,000.

BETWEEN with Dates

BETWEEN also works with date data types:

-- Employees hired in 2023
SELECT * FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN '2023-01-01' AND '2023-12-31';

The IN Operator

The IN operator lets us specify multiple values in a single condition. It is equivalent to writing multiple OR conditions.

IN Syntax

SELECT * FROM table_name
WHERE column IN (grades1, grades2, grades3);

IN Example

-- Employees in the IT, HR, or Marketing department
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT IN ('IT', 'HR', 'MARKETING');

Without IN, we would have to write:

-- Equivalent to the IN above
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT = 'IT'
   OR DEPARTMENT = 'HR'
   OR DEPARTMENT = 'MARKETING';

NOT IN

Excludes several values:

-- Employees outside the IT and HR departments
SELECT * FROM EMPLOYEE
WHERE DEPARTMENT NOT IN ('IT', 'HR');

IN with a Subquery

-- Employees in cities that have a branch office
SELECT * FROM EMPLOYEE
WHERE CITY IN (SELECT CITY FROM BRANCH_OFFICE);

Complete Example

The EMPLOYEE table:

-- Salary between 4 and 6 million
SELECT NAME, SALARY FROM EMPLOYEE
WHERE SALARY BETWEEN 4000000 AND 6000000;
-- Result: John (4.5M), Eric (5.5M)

-- Employees in Chicago or New York
SELECT NAME, CITY FROM EMPLOYEE
WHERE CITY IN ('Chicago', 'New York');
-- Result: Mike, John, Dana, Eric

-- Salary outside the 4-6 million range in Chicago or New York
SELECT NAME, SALARY, CITY FROM EMPLOYEE
WHERE SALARY NOT BETWEEN 4000000 AND 6000000
  AND CITY IN ('Chicago', 'New York');
-- Result: Mike (7M, Chicago), Dana (8M, Chicago)

Combining BETWEEN and IN

-- Employees earning 4-7 million in IT or HR
SELECT * FROM EMPLOYEE
WHERE SALARY BETWEEN 4000000 AND 7000000
  AND DEPARTMENT IN ('IT', 'HR');

Usage Tips

1. BETWEEN is always inclusive — remember this when choosing range bounds
2. IN is easier to read than many consecutive OR conditions
3. Use NOT IN with care — if the list contains a NULL, the results can be unexpected
4. For date ranges, make sure the date format matches the DBMS being used

We can now filter data in many ways. The next chapter covers JOIN — how to combine data from multiple tables.