Filtering data in SQL is a fundamental aspect of querying databases effectively. It allows you to retrieve specific subsets of data that meet certain criteria, making your queries more targeted and efficient. This guide will explore the essential techniques for filtering data using the WHERE clause, comparison operators, and logical operators in SQL.

Using WHERE Clause for Filtering

The WHERE clause is used in SQL to specify conditions that must be met for the rows to be included in the result set of a query.

Syntax:

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

• column1, column2, ...: Columns you want to retrieve from the table.
• table_name: Name of the table from which you are retrieving data.
• condition: Specifies the criteria that must be met. This can include comparisons and logical operations.

Using Comparison Operators

Comparison operators in SQL allow you to compare values in the WHERE clause to determine if a row should be included in the result set. Here are some commonly used comparison operators:

• Equal to (=): Matches a specific value.

  WHERE column_name = value;

• Not equal to (<> or !=): Excludes rows that match a specific value.

  WHERE column_name <> value;

• Greater than (>), Less than (<): Compares numeric or date values.

  WHERE column_name > value;
  WHERE column_name < value;

• Greater than or equal to (>=), Less than or equal to (<=): Includes rows with values greater than or equal to or less than or equal to a specified value.

  WHERE column_name >= value;
  WHERE column_name <= value;

Using Logical Operators

Logical operators in SQL allow you to combine multiple conditions in the WHERE clause to create more complex filtering criteria.

• AND: Requires both conditions to be true for the row to be included.

  WHERE condition1 AND condition2;

• OR: Requires at least one of the conditions to be true for the row to be included.

  WHERE condition1 OR condition2;

• NOT: Negates a condition, selecting rows that do not match the specified condition.

  WHERE NOT condition;

Example of Combined Filtering

This below SQL query retrieves all columns (*) from the employees table where the department is 'IT' and the salary is greater than 50000.

SELECT * 
FROM employees 
WHERE department = 'IT' 
  AND salary > 50000;