Filtering Data Using WHERE Clause & Operators
1. What is WHERE Clause?
The WHERE clause is used to filter rows that meet a specific condition.
Without it, SELECT returns all rows.
Syntax:
SELECT column1, column2
FROM table_name
WHERE condition;
2. Comparison Operators
Used to compare values.
| Operator | Description | Example |
|---|---|---|
| = | Equal to | salary = 35000 |
| != or <> | Not equal | age <> 25 |
| > | Greater than | salary > 30000 |
| < | Less than | age < 30 |
| >= | Greater or equal | salary >= 40000 |
| <= | Less or equal | age <= 22 |
Example:
SELECT * FROM employees
WHERE salary > 35000;
3. Logical Operators
Combine multiple conditions.
| Operator | Description | Example |
| ——– | ——————– | ————————————– |
| AND | Both conditions true | salary > 35000 AND department = ‘IT’ |
| OR | Any condition true | department = ‘IT’ OR department = ‘HR’ |
| NOT | Negates condition | NOT department = ‘HR’ |
Example:
SELECT * FROM employees
WHERE department = ‘IT’ AND salary > 35000;
4. Pattern Matching with LIKE
Search for patterns in text columns.
| Pattern | Description |
|---|---|
| % | Any number of characters |
| _ | Single character |
Examples:
— Names starting with ‘R’
SELECT * FROM employees
WHERE emp_name LIKE ‘R%’;
— Names ending with ‘a’
SELECT * FROM employees
WHERE emp_name LIKE ‘%a’;
— Names with 4 letters
SELECT * FROM employees
WHERE emp_name LIKE ‘____’;
5. Filtering with BETWEEN
Select values within a range.
SELECT * FROM employees
WHERE salary BETWEEN 35000 AND 42000;
6. Filtering with IN
Select values from a list.
SELECT * FROM employees
WHERE department IN (‘IT’, ‘HR’);
7. Filtering with IS NULL / IS NOT NULL
Check for missing values.
SELECT * FROM employees
WHERE department IS NULL;
SELECT * FROM employees
WHERE department IS NOT NULL;
