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:
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:
In this example:
The
SELECT
statement retrieves thefirst_name
,last_name
, andsalary
columns from theemployees
table, but only for employees whose salary is greater than 50,000.
Result:
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:
In this example:
The
SELECT
statement retrieves thefirst_name
,last_name
,department
, andsalary
columns from theemployees
table, but only for employees who work in theSales
department and have a salary greater than 50,000.
Result:
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:
In this example:
The
SELECT
statement retrieves thefirst_name
,last_name
,department
, andsalary
columns from theemployees
table, but includes rows where the employee either works in theSales
department or has a salary greater than 55,000.
Result:
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:
In this example:
The
SELECT
statement retrieves thefirst_name
,last_name
,department
, andsalary
columns from theemployees
table, but excludes rows where the department isMarketing
.
Result:
The
NOT
operator negates the condition, so only rows where the department is notMarketing
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:
In this example:
The
SELECT
statement retrieves thefirst_name
,last_name
,department
, andsalary
columns from theemployees
table, but only for employees who work in either theSales
orMarketing
department and have a salary greater than 50,000.
Result:
The parentheses ensure that the
OR
condition is evaluated first, followed by theAND
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.
Last updated