The Project | About Us | Contribute | Donations | License

HOME

In this section, we will explore the IN and BETWEEN operators in SQL, which are essential for filtering data based on specific criteria. These operators allow you to simplify your queries and make them more readable.

IN Operator

The IN operator is used to filter data based on a list of specified values. It is a shorthand for multiple OR conditions.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example

Consider a table employees with the following data:

employee_id	first_name	last_name	department
1	John	Doe	HR
2	Jane	Smith	IT
3	Alice	Johnson	Finance
4	Bob	Brown	IT
5	Carol	Davis	HR

To select employees who work in the IT or HR departments, you can use the IN operator:

SELECT first_name, last_name, department
FROM employees
WHERE department IN ('IT', 'HR');

Result

first_name	last_name	department
John	Doe	HR
Jane	Smith	IT
Bob	Brown	IT
Carol	Davis	HR

Practical Exercise

Exercise: Write a query to find employees whose employee_id is either 1, 3, or 5.

Solution:

SELECT first_name, last_name, employee_id
FROM employees
WHERE employee_id IN (1, 3, 5);

BETWEEN Operator

The BETWEEN operator is used to filter data within a specified range. It is inclusive, meaning it includes the boundary values.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example

Consider the same employees table. To select employees with employee_id between 2 and 4, you can use the BETWEEN operator:

SELECT first_name, last_name, employee_id
FROM employees
WHERE employee_id BETWEEN 2 AND 4;

Result

first_name	last_name	employee_id
Jane	Smith	2
Alice	Johnson	3
Bob	Brown	4

Practical Exercise

Exercise: Write a query to find employees whose employee_id is between 1 and 3.

Solution:

SELECT first_name, last_name, employee_id
FROM employees
WHERE employee_id BETWEEN 1 AND 3;

Common Mistakes and Tips

Common Mistakes

Using IN with a single value: If you only have one value, use = instead of IN.

-- Incorrect
SELECT * FROM employees WHERE department IN ('IT');

-- Correct
SELECT * FROM employees WHERE department = 'IT';

Misunderstanding BETWEEN inclusivity: Remember that BETWEEN includes the boundary values.

-- This will include employee_id 2 and 4
SELECT * FROM employees WHERE employee_id BETWEEN 2 AND 4;

Tips

Use IN for readability when dealing with multiple OR conditions.
Use BETWEEN for range conditions to make your queries more concise.

Conclusion

In this section, we covered the IN and BETWEEN operators, which are powerful tools for filtering data based on specific values or ranges. By mastering these operators, you can write more efficient and readable SQL queries. In the next section, we will delve into handling NULL values and the IS NULL operator.

IN and BETWEEN Operators

IN Operator

Syntax

Example

Result

Practical Exercise

BETWEEN Operator

Syntax

Example

Result

Practical Exercise

Common Mistakes and Tips

Common Mistakes

Tips

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project