Filtering Data with WHERE in SQL

Filtering Data with WHERE in SQL

The WHERE clause in SQL is used to filter data retrieved by a SELECT statement. By applying conditions in the WHERE clause, you can specify which rows should be included in the result set. This is particularly useful when you want to retrieve only the data that meets certain criteria. In this section, we'll explore how to use conditions and comparison operators, as well as how to combine conditions using AND, OR, and NOT.

---

1. Conditions and Comparison Operators

The WHERE clause allows you to specify conditions that the data must meet to be included in the result set. You can use various comparison operators to compare values in the table with specific criteria.

Syntax:

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

• condition: The condition that must be met for a row to be included in the result set.

Comparison Operators:

• =: 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;

In this example:

• The SELECT statement retrieves the first_name, last_name, and salary columns from the employees table, but only for employees whose salary is greater than 50,000.

Result:

first_name	last_name	salary
Amit	Sharma	60000.00

• Only rows where the salary is greater than 50,000 are included in the result.

---

2. Combining Conditions with AND, OR, and NOT

In addition to using individual conditions, you can combine multiple conditions using the logical operators AND, OR, and NOT. These operators allow you to create more complex filtering criteria.

a. Using AND

The AND operator is used to combine conditions that must all be true for a row to be included in the result set.

Example:

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

In this example:

• The SELECT statement retrieves the first_name, last_name, department, and salary columns from the employees table, but only for employees who work in the Sales department and have a salary greater than 50,000.

Result:

first_name	last_name	department	salary
Amit	Sharma	Sales	60000.00

• Both conditions must be true for the row to be included in the result.

b. Using OR

The OR operator is used to combine conditions where at least one of the conditions must be true for a row to be included in the result set.

Example:

SELECT first_name, last_name, department, salary 
FROM employees
WHERE department = 'Sales' OR salary > 55000;

In this example:

• The SELECT statement retrieves the first_name, last_name, department, and salary columns from the employees table, but includes rows where the employee either works in the Sales department or has a salary greater than 55,000.

Result:

first_name	last_name	department	salary
Amit	Sharma	Sales	60000.00
Neha	Singh	Marketing	55000.00

• If either condition is true, the row is included in the result.

c. Using NOT

The NOT operator is used to negate a condition, meaning that the condition must be false for the row to be included in the result set.

Example:

SELECT first_name, last_name, department, salary 
FROM employees
WHERE NOT department = 'Marketing';

In this example:

• The SELECT statement retrieves the first_name, last_name, department, and salary columns from the employees table, but excludes rows where the department is Marketing.

Result:

first_name	last_name	department	salary
Amit	Sharma	Sales	60000.00

• The NOT operator negates the condition, so only rows where the department is not Marketing are included.

d. Combining AND, OR, and NOT

You can combine AND, OR, and NOT in a single query to create complex filtering criteria. When combining these operators, you can use parentheses to control the order of evaluation.

Example:

SELECT first_name, last_name, department, salary 
FROM employees
WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 50000;

In this example:

• The SELECT statement retrieves the first_name, last_name, department, and salary columns from the employees table, but only for employees who work in either the Sales or Marketing department and have a salary greater than 50,000.

Result:

first_name	last_name	department	salary
Amit	Sharma	Sales	60000.00
Neha	Singh	Marketing	55000.00

• The parentheses ensure that the OR condition is evaluated first, followed by the AND condition.

---

Conclusion

The WHERE clause in SQL is a powerful tool for filtering data based on specific conditions. By using comparison operators and logical operators like AND, OR, and NOT, you can create complex queries that retrieve only the data that meets your criteria. Understanding how to use these operators effectively is essential for making the most of your SQL queries.

This tutorial is brought to you by codes with pankaj.