How to Filter Data with WHERE in SQL

In SQL, the WHERE clause is used to filter records based on specific conditions. It allows you to retrieve only the rows that meet the specified criteria, making your queries more precise and relevant to your needs. You can use WHERE to filter data based on different operators like comparison operators, logical operators, and pattern matching.

Here’s a detailed explanation of how to use the WHERE clause in SQL.

Basic Syntax of WHERE Clause

The basic syntax of the WHERE clause is as follows:

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

• column1, column2, ...: The columns you want to retrieve.
• table_name: The table from which you are fetching the data.
• condition: The condition that filters the data.

Example:

📄
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales';

This query retrieves the

Comparison Operators in WHERE Clause

The WHERE clause supports various comparison operators to filter data:

• =: Equal to
• != or <>: Not equal to
• >: Greater than
• <: Less than
• >=: Greater than or equal to
• <=: Less than or equal to

Example:

📄
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

This query retrieves employees whose salary is greater than 50,000.

Logical Operators in WHERE Clause

You can combine multiple conditions using logical operators such as AND, OR, and NOT.

• AND: Returns true if both conditions are true.
• OR: Returns true if at least one of the conditions is true.
• NOT: Reverses the result of a condition.

Example with AND:

📄
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' AND salary > 50000;

This query retrieves employees in the 'Sales' department whose salary is greater than 50,000.

Example with OR:

📄
SELECT first_name, last_name
FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

This query retrieves employees who work in either the 'Sales' or 'Marketing' department.

Pattern Matching with LIKE

The LIKE operator is used to search for a specified pattern in a column. You can use wildcards with LIKE:

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

Example:

📄
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'J%';

This query retrieves employees whose first name starts with the letter 'J'.

Example with underscore:

📄
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE '_an';

This query retrieves employees whose first name is three characters long and ends with 'an' (e.g., 'Dan', 'Jan').

Using IN for Multiple Values

The IN operator allows you to specify multiple values in the WHERE clause. It simplifies the query when you need to check for several values.

Example:

📄
SELECT first_name, last_name
FROM employees
WHERE department IN ('Sales', 'Marketing', 'IT');

This query retrieves employees who work in the 'Sales', 'Marketing', or 'IT' departments.

Checking for NULL Values

The IS NULL or IS NOT NULL operators are used to filter data based on NULL values. NULL represents unknown or missing data.

Example:

📄
SELECT first_name, last_name
FROM employees
WHERE hire_date IS NULL;

This query retrieves employees who do not have a hire date (i.e., the value is NULL).

Example with IS NOT NULL:

📄
SELECT first_name, last_name
FROM employees
WHERE hire_date IS NOT NULL;

This query retrieves employees who have a hire date.

Using BETWEEN for Range Values

The BETWEEN operator is used to filter the results within a specific range. It is often used with numeric, date, or time values.

Example:

📄
SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 70000;

This query retrieves employees whose salary is between 40,000 and 70,000 (inclusive).

Conclusion

The WHERE clause is essential for filtering data in SQL queries. By using various operators such as comparison operators, logical operators, LIKE, IN, BETWEEN, and handling NULL values, you can refine your query results to meet your specific needs. It’s an important tool to create efficient and effective SQL queries for retrieving relevant data.