SQL IN & BETWEEN Operators ⚖️¶

Mentor's Note: writing WHERE city = 'Surat' OR city = 'Mumbai' OR city = 'Delhi' is exhausting. IN and BETWEEN are the "Shorthand" of SQL. They make your code cleaner and more professional. 💡

🌟 The Scenario: The Online Shopping Filter 🛒¶

Imagine you are shopping for a new laptop.

BETWEEN (The Range): "I want a laptop priced between ₹40,000 and ₹60,000." 💸
IN (The Set): "I only want laptops from brands: 'Dell', 'HP', or 'Apple'." 💻

💻 1. The BETWEEN Operator (Ranges)¶

BETWEEN is inclusive, meaning it includes the start and end values.

-- Scenario: Find products in a budget range
SELECT product_name, price
FROM products
WHERE price BETWEEN 100 AND 500;
-- (Equivalent to price >= 100 AND price <= 500)

Filtering Dates with BETWEEN¶

-- Scenario: Find sales made in January
SELECT * FROM sales
WHERE sale_date BETWEEN '2026-01-01' AND '2026-01-31';

💻 2. The IN Operator (Sets)¶

IN allows you to specify multiple possible values in a single list.

-- Scenario: Filter employees by specific departments
SELECT emp_name, dept_name
FROM employees
WHERE dept_name IN ('IT', 'HR', 'Finance');

💻 3. The NOT Version (Exclusion) 🚫¶

You can combine these with NOT to exclude data.

-- Scenario: Find everyone EXCEPT those in the specific cities
SELECT * FROM customers
WHERE city NOT IN ('Surat', 'Mumbai');

-- Scenario: Find items OUTSIDE the standard price range
SELECT * FROM products
WHERE price NOT BETWEEN 100 AND 1000;

🎨 Visual Logic: Set vs Range¶

IN (Set):      [A]  [B]  [C]  <-- Selects specific "Dots"
BETWEEN (Range): [=========]  <-- Selects a continuous "Line"

💡 Pro Tip¶

"Use IN with subqueries! For example: WHERE id IN (SELECT id FROM winners). This is one of the most powerful patterns in SQL for linking datasets."