Filtering (WHERE, LIKE, IN, BETWEEN)

Filtering is a key part of writing useful SQL queries. It allows you to control which rows are returned or affected by your statements. The WHERE clause, combined with operators like LIKE, IN, and BETWEEN, makes filtering powerful and flexible.

---

WHERE

The WHERE clause filters rows based on a condition.

Basic Syntax:

SELECT column1, column2
FROM table_name
WHERE condition;

Example:

SELECT * FROM employees
WHERE department = 'Sales';

Key Points:

• Used with SELECT, UPDATE, DELETE.
• Conditions can use =, <>, <, >, <=, >=, AND, OR, NOT.

---

LIKE

The LIKE operator is used in the WHERE clause to search for a pattern in a column, often combined with wildcard characters.

Basic Syntax:

SELECT * FROM table_name
WHERE column LIKE pattern;

Example:

SELECT * FROM customers
WHERE name LIKE 'A%';

Common Wildcards:

• % : Represents zero or more characters.
• _ : Represents a single character.

Example:

SELECT * FROM products
WHERE product_code LIKE 'AB_1%';

---

IN

The IN operator tests whether a value matches any value in a list of values.

Basic Syntax:

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

Example:

SELECT * FROM orders
WHERE status IN ('Pending', 'Processing');

Key Points:

• Can be used with subqueries.
• Makes complex OR conditions more concise.

---

BETWEEN

The BETWEEN operator selects values within a range (inclusive).

Basic Syntax:

SELECT * FROM table_name
WHERE column BETWEEN value1 AND value2;

Example:

SELECT * FROM employees
WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

Key Points:

• Works for numeric, date, and text ranges.
• BETWEEN is inclusive of both endpoints.

---

Combining Filters

You can combine WHERE, LIKE, IN, and BETWEEN with AND, OR, and parentheses for more complex filters.

Example:

SELECT * FROM products
WHERE category = 'Books'
  AND price BETWEEN 10 AND 50
  AND title LIKE 'SQL%';

---

Summary

Filter	Purpose	Example
WHERE	Filter rows	WHERE salary > 50000
LIKE	Match patterns	LIKE 'A%'
IN	Match any in a list	IN ('Active', 'Pending')
BETWEEN	Match a range	BETWEEN 1 AND 10

Filtering is a core skill for writing precise, efficient queries. Master these operators to get exactly the data you need.