Filtering Data in SQL

1. Using the WHERE Clause

The WHERE clause is used to filter records in a SQL query based on specific conditions. Example:

SELECT * FROM users WHERE age > 25;

This query retrieves all users where the `age` is greater than 25.

Additional Example:

SELECT * FROM orders WHERE order_date = '2024-10-01';

This query retrieves all orders that were placed on October 1, 2024.

Example with Multiple Conditions:

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

This query retrieves all employees who work in the IT department and earn more than 50,000.

2. Comparison Operators

SQL supports several comparison operators to filter data. Commonly used operators include:

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

Example using comparison operators:

SELECT * FROM products WHERE price >= 100;

This query retrieves all products with a price of 100 or more.

Example with Range of Values:

SELECT * FROM customers WHERE age BETWEEN 30 AND 40;

This query retrieves all customers whose age is between 30 and 40.

3. Logical Operators: AND, OR, NOT

SQL also provides logical operators to combine multiple conditions. The most common logical operators are:

• AND: Returns records that meet all conditions.
• OR: Returns records that meet at least one condition.
• NOT: Excludes records that meet the condition.

AND Operator Example:

SELECT * FROM users WHERE age > 25 AND city = 'New York';

This query retrieves all users who are older than 25 and live in New York.

OR Operator Example:

SELECT * FROM users WHERE age < 18 OR age > 65;

This query retrieves all users who are either younger than 18 or older than 65.

NOT Operator Example:

SELECT * FROM users WHERE NOT city = 'Los Angeles';

This query retrieves all users who do not live in Los Angeles.