IN, BETWEEN, LIKE

IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. It's a shorthand for multiple OR conditions.

Basic Syntax

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

Sample Data

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50),
    country VARCHAR(50),
    status VARCHAR(20) DEFAULT 'active'
);

INSERT INTO customers (name, city, country, status)
VALUES
    ('Alice', 'New York', 'USA', 'premium'),
    ('Bob', 'London', 'UK', 'active'),
    ('Charlie', 'Paris', 'France', 'active'),
    ('Diana', 'Tokyo', 'Japan', 'premium'),
    ('Eve', 'Berlin', 'Germany', 'inactive'),
    ('Frank', 'New York', 'USA', 'active');

IN Examples

-- Find customers in specific cities
SELECT name, city FROM customers
WHERE city IN ('New York', 'London', 'Paris');

-- Find premium and active customers
SELECT name, status FROM customers
WHERE status IN ('premium', 'active');

-- Equivalent OR conditions
SELECT name, city FROM customers
WHERE city = 'New York' OR city = 'London' OR city = 'Paris';

-- IN with numbers
SELECT * FROM orders
WHERE customer_id IN (1, 3, 5, 7);

-- NOT IN: Exclude specific values
SELECT name, city FROM customers
WHERE country NOT IN ('USA', 'UK');

IN with Subqueries

-- Find customers who have placed orders
SELECT name FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

-- Find products in categories that have sales
SELECT * FROM products
WHERE category_id IN (
    SELECT category_id FROM categories WHERE is_active = TRUE
);

IN Performance

-- IN is typically optimized for small lists
SELECT * FROM customers
WHERE customer_id IN (1, 2, 3, 4, 5);

-- For large lists, consider using a temporary table
CREATE TEMPORARY TABLE temp_ids (id INT);
INSERT INTO temp_ids VALUES (1), (2), (3), ... ;
SELECT * FROM customers WHERE customer_id IN (SELECT id FROM temp_ids);

IN Best Practices

1. Keep lists small - IN is efficient for small value lists
2. Use consistent data types - Match column and value types
3. Consider EXISTS for subqueries with large result sets
4. Avoid NULL in IN lists - NULL behavior can be tricky

-- NULL in IN list
SELECT * FROM customers WHERE customer_id IN (1, 2, NULL);
-- Returns rows where customer_id is 1 or 2
-- Does NOT return NULL customer_ids

-- To handle NULLs properly
SELECT * FROM customers
WHERE customer_id IN (1, 2) OR customer_id IS NULL;

The IN operator makes queries more readable and often more efficient than multiple OR conditions.

BETWEEN Operator

The BETWEEN operator selects values within a given range. It's inclusive, meaning it includes the boundary values.

Basic Syntax

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

BETWEEN Examples

-- Find products with price between 100 and 500
SELECT name, price FROM products
WHERE price BETWEEN 100 AND 500;
-- Equivalent to: price >= 100 AND price <= 500

-- Find customers in a date range
SELECT name, created_at FROM customers
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Find orders with quantity between 5 and 10
SELECT * FROM order_items
WHERE quantity BETWEEN 5 AND 10;

-- NOT BETWEEN: Exclude range
SELECT name, price FROM products
WHERE price NOT BETWEEN 100 AND 500;
-- Returns prices < 100 or > 500

BETWEEN with Dates

-- Date ranges (inclusive of both dates)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

-- Current month orders
SELECT * FROM orders
WHERE order_date BETWEEN 
    DATE_FORMAT(CURDATE(), '%Y-%m-01') 
    AND LAST_DAY(CURDATE());

-- Last 30 days
SELECT * FROM orders
WHERE order_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE();

BETWEEN with Strings

-- Alphabetical range
SELECT name FROM customers
WHERE name BETWEEN 'A' AND 'M';
-- Returns names starting with A through M

-- Phone number range
SELECT * FROM contacts
WHERE phone BETWEEN '555-0000' AND '555-9999';

BETWEEN vs Comparison Operators

-- BETWEEN (inclusive on both ends)
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- Equivalent comparison operators
SELECT * FROM products WHERE price >= 100 AND price <= 500;

-- For exclusive ranges, use comparison operators
SELECT * FROM products WHERE price > 100 AND price < 500;

BETWEEN Performance

-- BETWEEN can use indexes effectively
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Avoid functions on indexed columns
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;  -- Slower, can't use index

-- Better approach
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';  -- Uses index

BETWEEN Best Practices

1. Inclusive boundaries - Both values are included
2. Use consistent types - Column and values must be compatible
3. Consider NULL handling - NULL values are not included
4. For exclusive ranges - Use > and < operators

-- NULL is not included in BETWEEN
SELECT * FROM products
WHERE price BETWEEN 100 AND 500;
-- Does not return rows where price is NULL

-- To include NULLs
SELECT * FROM products
WHERE price BETWEEN 100 AND 500 OR price IS NULL;

BETWEEN is clean and readable for range queries, making it preferred over multiple comparison operators.

LIKE Operator

The LIKE operator is used for pattern matching in string columns. It uses two wildcards:

• % - Matches any sequence of zero or more characters
• _ - Matches exactly one character

Basic Syntax

SELECT column1, column2
FROM table_name
WHERE column LIKE pattern;

LIKE Examples

-- % wildcard examples
SELECT * FROM customers WHERE name LIKE 'A%';      -- Starts with A
SELECT * FROM customers WHERE name LIKE '%a';       -- Ends with a
SELECT * FROM customers WHERE name LIKE '%smith%';  -- Contains smith
SELECT * FROM customers WHERE email LIKE '%@gmail.com'; -- Gmail users

-- _ wildcard examples
SELECT * FROM products WHERE code LIKE 'P___';     -- 4 chars starting with P
SELECT * FROM orders WHERE order_number LIKE 'ORD-____-__';

-- Combining wildcards
SELECT * FROM customers WHERE phone LIKE '(555) %';
SELECT * FROM products WHERE sku LIKE 'ELEC-___-%';

Case Sensitivity

-- Case sensitivity depends on database and collation
-- MySQL: case-insensitive by default with utf8 collation
SELECT * FROM customers WHERE name LIKE 'john%';  -- Matches John, john, JOHN

-- PostgreSQL: case-sensitive by default
SELECT * FROM customers WHERE name LIKE 'john%';  -- Only matches 'john...'
SELECT * FROM customers WHERE name ILIKE 'john%'; -- Case-insensitive

-- Force case sensitivity in MySQL
SELECT * FROM customers WHERE BINARY name LIKE 'John%';

LIKE with Escape Character

-- Search for literal % or _
SELECT * FROM products WHERE description LIKE '%100\\%%' ESCAPE '\\';
-- Matches: '100% off', 'Discount 100%', etc.

SELECT * FROM products WHERE code LIKE '%\\_test%' ESCAPE '\\';
-- Matches: 'my_test', 'test_data', etc.

LIKE Performance

-- Leading wildcard (slow - can't use index)
SELECT * FROM customers WHERE name LIKE '%smith%';

-- Trailing wildcard (fast - can use index)
SELECT * FROM customers WHERE name LIKE 'smith%';

-- Exact match (fastest)
SELECT * FROM customers WHERE name = 'Smith';

LIKE vs Other Operators

-- For exact match, use =
SELECT * FROM customers WHERE name = 'John';

-- For prefix match, use LIKE or >=
SELECT * FROM customers WHERE name LIKE 'John%';
-- OR
SELECT * FROM customers WHERE name >= 'John' AND name < 'Johm';

-- For contains, use LIKE or FULLTEXT
SELECT * FROM products WHERE description LIKE '%wireless%';
-- OR (better performance)
SELECT * FROM products WHERE MATCH(description) AGAINST('wireless');

LIKE Best Practices

1. Avoid leading wildcards when possible for performance
2. Use specific patterns - More specific = faster
3. Consider INDEX usage - Trailing wildcards can use indexes
4. Use ESCAPE for literal % and _ characters

-- Good: Trailing wildcard (index-friendly)
SELECT * FROM customers WHERE name LIKE 'John%';

-- Bad: Leading wildcard (full table scan)
SELECT * FROM customers WHERE name LIKE '%John%';

-- Better alternative for contains search
-- Use FULLTEXT index or search engine

LIKE is essential for flexible string matching, but use it judiciously for optimal performance.

NULL Handling

NULL represents missing or unknown data. It's not a value - it's the absence of a value. This makes NULL behavior different from other values.

NULL Basics

-- NULL is not equal to anything
SELECT * FROM customers WHERE phone = NULL;    -- Returns nothing!
SELECT * FROM customers WHERE phone != NULL;   -- Returns nothing!
SELECT * FROM customers WHERE phone <> NULL;   -- Returns nothing!

-- Correct way to check NULL
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;

-- NULL in expressions
SELECT 1 + NULL;  -- Returns NULL
SELECT 'Hello' || NULL;  -- Returns NULL
SELECT NULL * 5;  -- Returns NULL

NULL in Different Contexts

-- NULL in WHERE clause
SELECT * FROM customers WHERE phone IS NULL;

-- NULL in ORDER BY (NULLs first or last depending on DB)
SELECT name, phone FROM customers ORDER BY phone;
-- MySQL: NULLs first in ASC, last in DESC
-- PostgreSQL: NULLs last in ASC, first in DESC

-- NULL in aggregate functions
SELECT COUNT(*) FROM customers;           -- Counts all rows
SELECT COUNT(phone) FROM customers;      -- Excludes NULLs
SELECT COUNT(DISTINCT phone) FROM customers;  -- Excludes NULLs

-- NULL in arithmetic
SELECT price * quantity AS total FROM orders;
-- If either price or quantity is NULL, total is NULL

Handling NULLs

-- COALESCE: Return first non-NULL value
SELECT name, COALESCE(phone, 'No phone') AS phone
FROM customers;

-- NVL (Oracle) or IFNULL (MySQL)
SELECT name, IFNULL(phone, 'No phone') AS phone
FROM customers;

-- NULLIF: Return NULL if values are equal
SELECT NULLIF(price, 0) AS price  -- Returns NULL if price is 0
FROM products;

-- CASE with NULL
SELECT name,
    CASE 
        WHEN phone IS NULL THEN 'No phone'
        ELSE phone
    END AS phone_status
FROM customers;

NULL in Comparisons

-- NULL with IN
SELECT * FROM customers WHERE customer_id IN (1, 2, NULL);
-- Returns customer_id 1 and 2, NOT NULL values

-- NULL with NOT IN
SELECT * FROM customers WHERE customer_id NOT IN (1, 2);
-- Returns all except 1 and 2, INCLUDING NULL rows

-- NULL with EXISTS
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Works correctly with NULLs

-- NULL with BETWEEN
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
-- Does NOT return rows where price is NULL

NULL in GROUP BY

-- NULLs are grouped together
SELECT phone, COUNT(*) AS count
FROM customers
GROUP BY phone;
-- One group for NULL phone values

-- NULL in HAVING
SELECT phone, COUNT(*) AS count
FROM customers
GROUP BY phone
HAVING phone IS NULL;  -- Filter groups with NULL phone

NULL Best Practices

1. Always use IS NULL/IS NOT NULL - Don't use = or !=
2. Use COALESCE for default values
3. Design carefully - Avoid unnecessary NULLs
4. Use NOT NULL constraints when appropriate
5. Consider three-valued logic in complex conditions

-- Good design: Use NOT NULL with DEFAULT
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) DEFAULT 'N/A',
    status VARCHAR(20) DEFAULT 'active' NOT NULL
);

-- Avoid: Nullable columns where data is required
CREATE TABLE bad_design (
    id INT PRIMARY KEY,
    name VARCHAR(100),  -- Should be NOT NULL
    email VARCHAR(100)  -- Should be NOT NULL
);

Understanding NULL behavior is crucial for writing correct SQL queries and designing robust databases.