WHERE Clause and Filtering

Difficulty: Basic
Generated on: 2025-07-10 02:21:35
Category: SQL Cheatsheet for Database Development

---

SQL WHERE Clause and Filtering Cheatsheet (Basic Level)

1. Quick Overview

The WHERE clause in SQL is used to filter records based on specified conditions. It allows you to retrieve only the rows that meet certain criteria, making your queries more efficient and focused. It’s the cornerstone of data selection and reporting.

When to Use:

• Selecting specific rows from a table based on certain values.
• Creating conditional reports and summaries.
• Implementing data validation rules.
• Building dynamic SQL queries.

2. Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Key Components:

• SELECT: Specifies the columns to retrieve.
• FROM: Specifies the table to retrieve data from.
• WHERE: Introduces the filtering condition.
• condition: A boolean expression that evaluates to TRUE, FALSE, or UNKNOWN. Only rows where the condition is TRUE are returned.

3. Common Use Cases

• Filtering by exact match: WHERE column_name = 'value'
• Filtering by range: WHERE column_name BETWEEN value1 AND value2
• Filtering by comparison: WHERE column_name > value, WHERE column_name < value
• Filtering using LIKE for pattern matching: WHERE column_name LIKE 'pattern%'
• Filtering for NULL values: WHERE column_name IS NULL or WHERE column_name IS NOT NULL
• Combining conditions using AND and OR: WHERE condition1 AND condition2 or WHERE condition1 OR condition2
• Filtering based on a list of values using IN: WHERE column_name IN (value1, value2, value3)
• Filtering based on a subquery using EXISTS or NOT EXISTS: WHERE EXISTS (SELECT 1 FROM another_table WHERE condition)

4. Best Practices

• Use indexes: Ensure that columns used in WHERE clauses are indexed to speed up query execution. This is especially important for large tables.
• Avoid OR with non-indexed columns: OR conditions can sometimes prevent the database from using indexes effectively. Consider alternative query strategies if performance is poor.
• Use BETWEEN for range queries: BETWEEN is generally more efficient than using two separate comparison operators (>= and <=).
• Be mindful of data types: Ensure that the data types in the WHERE clause match the data types of the columns being filtered. Implicit type conversions can lead to unexpected results and performance issues.
• Use parameterized queries: To prevent SQL injection attacks, always use parameterized queries or prepared statements, especially when the WHERE clause includes user-supplied input.
• Test your queries: Always test your queries with representative data to ensure they return the expected results.
• Profile slow queries: Use database profiling tools to identify slow-running queries and optimize them.

5. Examples

Sample Data (Employees Table):

employee_id	first_name	last_name	department	salary	hire_date
1	John	Doe	Sales	60000	2022-01-15
2	Jane	Smith	Marketing	75000	2021-05-20
3	Peter	Jones	Sales	55000	2023-03-10
4	Mary	Brown	IT	90000	2020-11-01
5	David	Wilson	HR	65000	2022-08-05
6	Alice	Johnson	Marketing	80000	2021-12-15
7	Robert	Miller	IT	100000	2019-06-30
8	Sarah	Davis	Sales	70000	2022-04-22
9	Michael	Garcia	HR	72000	2023-01-08
10	Linda	Rodriguez	IT	95000	2020-03-18

Example 1: Employees in the Sales Department

SELECT employee_id, first_name, last_name
FROM Employees
WHERE department = 'Sales';

Output:

employee_id	first_name	last_name
1	John	Doe
3	Peter	Jones
8	Sarah	Davis

Example 2: Employees with a Salary Greater Than 70000

SELECT employee_id, first_name, last_name, salary
FROM Employees
WHERE salary > 70000;

Output:

employee_id	first_name	last_name	salary
2	Jane	Smith	75000
4	Mary	Brown	90000
6	Alice	Johnson	80000
7	Robert	Miller	100000
9	Michael	Garcia	72000
10	Linda	Rodriguez	95000

Example 3: Employees Hired Between 2022-01-01 and 2022-12-31

SELECT employee_id, first_name, last_name, hire_date
FROM Employees
WHERE hire_date BETWEEN '2022-01-01' AND '2022-12-31';

Output:

employee_id	first_name	last_name	hire_date
1	John	Doe	2022-01-15
5	David	Wilson	2022-08-05
8	Sarah	Davis	2022-04-22

Example 4: Employees with a Last Name Starting with ‘S’

SELECT employee_id, first_name, last_name
FROM Employees
WHERE last_name LIKE 'S%';

Output:

employee_id	first_name	last_name
2	Jane	Smith
8	Sarah	Davis

Example 5: Employees in the IT or HR Department

SELECT employee_id, first_name, last_name, department
FROM Employees
WHERE department IN ('IT', 'HR');

Output:

employee_id	first_name	last_name	department
4	Mary	Brown	IT
5	David	Wilson	HR
7	Robert	Miller	IT
9	Michael	Garcia	HR
10	Linda	Rodriguez	IT

Example 6: Employees NOT in the Sales Department

SELECT employee_id, first_name, last_name, department
FROM Employees
WHERE department <> 'Sales';  -- or WHERE department != 'Sales' (depending on database)

Output: (All employees except John Doe, Peter Jones, and Sarah Davis)

Example 7: Using AND and OR together (Pay attention to parentheses!)

SELECT employee_id, first_name, last_name, department, salary
FROM Employees
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 70000;

Output: (Employees either in Sales or Marketing, AND with a salary greater than 70000)

employee_id	first_name	last_name	department	salary
6	Alice	Johnson	Marketing	80000

6. Common Pitfalls

• Incorrect operator: Using = instead of LIKE for pattern matching, or vice versa.
• Case sensitivity: String comparisons can be case-sensitive or case-insensitive depending on the database and collation settings. Use appropriate functions (e.g., LOWER(), UPPER()) to handle case-insensitive comparisons.
• NULL value handling: You cannot use = to compare with NULL. You must use IS NULL or IS NOT NULL. WHERE column_name = NULL will always return FALSE.
• Operator precedence: Remember that AND has higher precedence than OR. Use parentheses to explicitly control the order of evaluation.
• String type mismatches: Make sure you are comparing strings to strings and numbers to numbers. Implicit conversions can lead to unexpected behavior.
• SQL Injection: Never directly embed user input into SQL queries. Use parameterized queries or prepared statements.
• Forgetting IS NULL or IS NOT NULL: Filtering for NULL values requires special syntax.
• Incorrect Date Formats: Be aware of the date format expected by your database. Use appropriate date functions or format strings.

7. Database Variations

While the core syntax of the WHERE clause is generally consistent across different databases, there are some variations:

• String Comparisons (Case Sensitivity):

  • MySQL: Case-insensitive by default for VARCHAR columns (unless a binary collation is used). Use BINARY keyword for case-sensitive comparisons.
  • PostgreSQL: Case-sensitive by default. Use ILIKE for case-insensitive comparisons (e.g., WHERE column_name ILIKE 'pattern%').
  • SQL Server: Case-insensitive by default (depending on the collation). Use COLLATE clause for explicit case sensitivity (e.g., WHERE column_name COLLATE Latin1_General_CS_AS = 'value').
  • Oracle: Case-sensitive by default. Use LOWER() or UPPER() functions for case-insensitive comparisons.

• String Concatenation:

  • MySQL: CONCAT(string1, string2)
  • PostgreSQL: string1 || string2
  • SQL Server: string1 + string2
  • Oracle: string1 || string2

• String Pattern Matching:

  • All databases support LIKE, however specific regular expression functions can vary.

• NULL Handling: The behavior of aggregate functions with NULL values is generally consistent, but there might be slight differences in how NULL values are handled in specific functions.

• Date and Time Functions: Date and time functions vary significantly across different databases. Consult the documentation for your specific database for details. For example, the function to get the current date and time is:

  • MySQL: NOW() or CURDATE()/CURTIME()
  • PostgreSQL: NOW() or CURRENT_DATE/CURRENT_TIME
  • SQL Server: GETDATE() or GETUTCDATE()
  • Oracle: SYSDATE or SYSTIMESTAMP

• Not Equal Operator: While most databases support <> for “not equal to,” some also support !=. Check your database documentation. <> is generally the more portable option.