Lesson 3: Filtering Data using WHERE Clause
In this lesson, we will explore filtering data in SQL using the WHERE clause. The WHERE clause allows us to specify conditions to retrieve specific subsets of data that meet certain criteria. Understanding how to use the WHERE clause effectively is crucial for retrieving relevant information from a database.
Introduction to WHERE Clause
The WHERE clause is used to filter data in SQL queries. It allows us to specify conditions that determine which rows should be included in the result set. The general syntax of a SELECT statement with a WHERE clause is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
In the above syntax, “condition” represents the filtering condition that specifies the criteria for retrieving specific rows.
Using Comparison Operators in WHERE Clause
Comparison operators are used to compare values and evaluate conditions in the WHERE clause. Here are the commonly used comparison operators:
- Equal to (=): Matches a specific value. For example,
column = value. - Not equal to (<> or !=): Does not match a specific value. For example,
column <> valueorcolumn != value. - Greater than (>): Matches values greater than a specific value. For example,
column > value. - Less than (<): Matches values less than a specific value. For example,
column < value. - Greater than or equal to (>=): Matches values greater than or equal to a specific value. For example,
column >= value. - Less than or equal to (<=): Matches values less than or equal to a specific value. For example,
column <= value.
These operators allow us to create conditions that filter rows based on specific comparisons.
Combining Multiple Conditions using Logical Operators
In some cases, we may need to combine multiple conditions in the WHERE clause. SQL provides logical operators like AND, OR, and NOT to create more complex filtering conditions. Here’s how they are used:
- AND operator: Retrieves rows that satisfy all specified conditions. For example,
condition1 AND condition2. - OR operator: Retrieves rows that satisfy at least one of the specified conditions. For example,
condition1 OR condition2. - NOT operator: Negates a condition, retrieving rows that do not satisfy the condition. For example,
NOT condition.
Logical operators help us create more specific conditions by combining multiple comparisons or negating conditions.
Handling NULL Values
To handle NULL values in filtering, we use the IS NULL and IS NOT NULL operators. Here’s how they are used:
- IS NULL: Matches rows where a column contains a NULL value. For example,
column IS NULL. - IS NOT NULL: Matches rows where a column does not contain a NULL value. For example,
column IS NOT NULL.
These operators are useful for filtering rows based on the presence or absence of NULL values.
Understanding how to use the WHERE clause effectively enables us to filter data and retrieve specific subsets based on desired criteria. In the next lesson, we will explore the LIKE operator and wildcard patterns for more flexible data filtering.