SQL - BETWEEN 運算子

Hello there, future SQL wizards! Today, we're going to embark on an exciting journey into the world of the BETWEEN operator. As your friendly neighborhood computer science teacher, I'm here to guide you through this adventure with plenty of examples and a dash of humor. So, grab your virtual wands (keyboards), and let's get started!

The SQL BETWEEN Operator

Imagine you're organizing a party, and you want to invite friends whose ages fall within a certain range. That's exactly what the BETWEEN operator does in SQL – it helps us select data within a specific range. Let's dive in!

Basic Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

This might look a bit intimidating at first, but don't worry! We'll break it down with some examples.

Example 1: Selecting Products within a Price Range

Let's say we have a table called "Products" in our online store database. We want to find all products priced between $50 and $100.

SELECT * FROM Products
WHERE Price BETWEEN 50 AND 100;

This query will return all columns (*) from the Products table where the Price is between $50 and $100, inclusive. It's like asking, "Show me all products that cost at least $50 but no more than $100."

Example 2: Date Ranges

The BETWEEN operator isn't just for numbers. It works great with dates too! Suppose we have an "Orders" table and want to find all orders placed in the year 2023.

SELECT * FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

This query fetches all orders with dates from January 1, 2023, to December 31, 2023. It's like asking your database, "What orders did we receive in 2023?"

BETWEEN Operator with IN Operator

Now, let's spice things up a bit! We can combine BETWEEN with the IN operator for more complex queries. Think of it as creating a guest list for your party with specific age ranges and names.

Example 3: Combining BETWEEN and IN

Suppose we want to find employees who are between 25 and 35 years old and work in either the IT or HR department.

SELECT * FROM Employees
WHERE (Age BETWEEN 25 AND 35)
AND Department IN ('IT', 'HR');

This query is like saying, "Show me all employees aged 25 to 35 who work in IT or HR." It's a great way to narrow down your search criteria.

BETWEEN Operator with UPDATE Statement

Sometimes, we need to update data within a specific range. Let's see how we can use BETWEEN in an UPDATE statement.

Example 4: Updating Salaries

Imagine we want to give a 10% raise to all employees earning between $50,000 and $70,000.

UPDATE Employees
SET Salary = Salary * 1.10
WHERE Salary BETWEEN 50000 AND 70000;

This query increases the salary by 10% for all employees in the specified salary range. It's like giving a bonus to your middle-income employees!

BETWEEN Operator with DELETE Statement

The BETWEEN operator can also be used with DELETE statements when you need to remove data within a specific range.

Example 5: Deleting Old Records

Let's say we want to delete all orders from 2020 to clean up our database.

DELETE FROM Orders
WHERE OrderDate BETWEEN '2020-01-01' AND '2020-12-31';

This query removes all orders placed in 2020. It's like doing a digital spring cleaning for your database!

NOT BETWEEN Operator

Now, what if we want to find data outside a specific range? That's where the NOT BETWEEN operator comes in handy.

Example 6: Finding Products Outside a Price Range

Let's find all products that are either cheaper than $20 or more expensive than $100.

SELECT * FROM Products
WHERE Price NOT BETWEEN 20 AND 100;

This query returns all products with prices less than $20 or greater than $100. It's like asking, "Show me the budget-friendly and luxury items, but nothing in between."

NOT BETWEEN Operator with IN

Lastly, let's combine NOT BETWEEN with the IN operator for even more precise queries.

Example 7: Complex Exclusion Query

Suppose we want to find employees who are not between 30 and 50 years old and don't work in the Sales or Marketing departments.

SELECT * FROM Employees
WHERE (Age NOT BETWEEN 30 AND 50)
AND Department NOT IN ('Sales', 'Marketing');

This query finds employees who are either under 30 or over 50, and who don't work in Sales or Marketing. It's like saying, "Show me the young and seasoned employees who aren't in our customer-facing departments."

Summary of BETWEEN Operator Methods

Here's a handy table summarizing the different ways we can use the BETWEEN operator:

方法	描述	示例
BETWEEN	選取給定範圍內的值	WHERE Price BETWEEN 50 AND 100
BETWEEN with Dates	選取給定日期範圍內的日期	WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
BETWEEN with IN	結合範圍和列表選擇	WHERE (Age BETWEEN 25 AND 35) AND Department IN ('IT', 'HR')
BETWEEN in UPDATE	更新範圍內的值	UPDATE Employees SET Salary = Salary * 1.10 WHERE Salary BETWEEN 50000 AND 70000
BETWEEN in DELETE	刪除範圍內的記錄	DELETE FROM Orders WHERE OrderDate BETWEEN '2020-01-01' AND '2020-12-31'
NOT BETWEEN	選取給定範圍外的值	WHERE Price NOT BETWEEN 20 AND 100
NOT BETWEEN with IN	結合排除範圍和列表	WHERE (Age NOT BETWEEN 30 AND 50) AND Department NOT IN ('Sales', 'Marketing')

And there you have it, folks! We've journeyed through the land of BETWEEN operators, from basic selections to complex queries. Remember, practice makes perfect, so don't be afraid to experiment with these queries on your own datasets. Happy SQL-ing, and may your queries always return the results you're looking for!

Credits: Image by storyset