MySQL - Between Operator: A Beginner's Guide

Hello there, aspiring database enthusiasts! Today, we're going to embark on an exciting journey into the world of MySQL, specifically focusing on the BETWEEN operator. Don't worry if you're new to programming; I'll be your friendly guide, explaining everything step-by-step. So, let's dive in!

What is the BETWEEN Operator?

Imagine you're organizing your bookshelf. You want to find all the books published between 2010 and 2020. That's exactly what the BETWEEN operator helps us do in MySQL – it allows us to select values within a given range.

Basic Syntax

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

Let's break this down:

• SELECT: This is like saying "I want to see..."
• column_name(s): These are the specific pieces of information you want to see
• FROM table_name: This is where the information is stored
• WHERE: This is how we set conditions
• BETWEEN value1 AND value2: This is our range

MySQL BETWEEN Operator in Action

Let's say we have a table called books with columns id, title, author, and publication_year. Here's how we might use BETWEEN:

SELECT title, author, publication_year
FROM books
WHERE publication_year BETWEEN 2010 AND 2020;

This query will show us all books published between 2010 and 2020, inclusive. It's like asking, "Show me all books from 2010 up to and including 2020."

BETWEEN with Dates

BETWEEN works great with dates too! Let's say we have an orders table:

SELECT order_id, customer_name, order_date
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

This will show all orders placed in 2023. Neat, right?

MySQL BETWEEN with IN Operator

Now, let's spice things up a bit. What if we want to find books published between 2010 and 2020, but only by certain authors? That's where we combine BETWEEN with IN:

SELECT title, author, publication_year
FROM books
WHERE publication_year BETWEEN 2010 AND 2020
AND author IN ('J.K. Rowling', 'George R.R. Martin', 'Stephen King');

This query is like saying, "Show me books from 2010 to 2020, but only if they're written by these specific authors."

MySQL BETWEEN with UPDATE Statement

Sometimes, we don't just want to view data – we want to change it. Let's say we want to give a 10% discount to all products priced between $50 and $100:

UPDATE products
SET price = price * 0.9
WHERE price BETWEEN 50 AND 100;

This query updates the prices, applying a 10% discount to products in our specified range.

BETWEEN Operator with DELETE Statement

The BETWEEN operator can also be used when we want to remove data. Imagine we want to delete all orders from the first quarter of 2023:

DELETE FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

This removes all orders placed in the first three months of 2023. Be careful with DELETE statements – always double-check before running them!

MySQL NOT BETWEEN Operator

Sometimes, we want everything outside a certain range. That's where NOT BETWEEN comes in handy:

SELECT title, author, publication_year
FROM books
WHERE publication_year NOT BETWEEN 2010 AND 2020;

This query shows all books published before 2010 or after 2020.

NOT BETWEEN Operator with IN Operator

We can combine NOT BETWEEN with IN for even more specific queries:

SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 50 AND 100
AND category IN ('Electronics', 'Appliances');

This query finds products in the Electronics or Appliances categories that are either under $50 or over $100.

Using the BETWEEN Operator in a Client Program

When using BETWEEN in a client program (like a PHP script), you might do something like this:

<?php
$min_price = 50;
$max_price = 100;

$query = "SELECT product_name, price FROM products WHERE price BETWEEN ? AND ?";
$stmt = $conn->prepare($query);
$stmt->bind_param("dd", $min_price, $max_price);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['product_name'] . ": $" . $row['price'] . "<br>";
}
?>

This PHP script prepares a query with placeholders, binds the values, executes the query, and then displays the results.

Conclusion

And there you have it, folks! We've journeyed through the world of the BETWEEN operator in MySQL. From basic selects to updates and deletes, we've seen how versatile this little operator can be. Remember, practice makes perfect, so don't be afraid to experiment with your own queries.

Here's a quick reference table of the methods we've covered:

Method	Description
BETWEEN	Selects values within a given range
BETWEEN with IN	Combines range and specific value selection
BETWEEN with UPDATE	Updates records within a specific range
BETWEEN with DELETE	Deletes records within a specific range
NOT BETWEEN	Selects values outside a given range
NOT BETWEEN with IN	Combines exclusion of a range with specific value selection

Happy querying, and may your databases always be organized and efficient!

Credits: Image by storyset