Lesson 16: Advanced Filtering: Logical and Range Operators

When basic comparisons aren't enough, we use logical operators to combine multiple conditions or range operators to check for specific sets of values.

Logical Operators

1. AND: Both conditions must be true.
2. OR: At least one condition must be true.
3. NOT: Negates the condition.

Example: Combining Conditions (AND)

Find employees who are in the 'Sales' department AND have a salary greater than 60,000.

sql SELECT Name, Department, Salary FROM Employees WHERE Department = 'Sales' AND Salary > 60000;

Example: Combining Conditions (OR)

Find employees in 'HR' OR 'Marketing'.

sql SELECT Name, Department FROM Employees WHERE Department = 'HR' OR Department = 'Marketing';

Range and Set Operators

1. IN: Checks if a value is present in a list of specified values.
2. BETWEEN: Checks for values within a specified range (inclusive).
3. LIKE: Used for pattern matching (text searching).

Example 1: Using IN

sql SELECT Name FROM Employees WHERE Department IN ('HR', 'Marketing', 'IT');

Example 2: Using BETWEEN

Find products priced between $10 and $50 (inclusive).

sql SELECT Name, Price FROM Products WHERE Price BETWEEN 10.00 AND 50.00;