Filtering Data with WHERE Clause

The WHERE clause in SQL is used to filter records based on specific conditions, making it a powerful tool for querying only the data you need from a database. It is used in conjunction with the SELECT statement and other SQL commands like UPDATE or DELETE.

Syntax Overview: The basic syntax of the WHERE clause is as follows:

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

Condition in WHERE Clause: The condition in the WHERE clause is a logical expression that evaluates to true, false, or unknown. Records for which the condition evaluates to true are included in the result set.

Using Various Operators in WHERE Clause

Equality and Inequality Operators:

• = is used to test for equality.
• <> or != is used to test for inequality.
• Example:

SELECT * FROM employees WHERE department = 'Finance';
SELECT * FROM employees WHERE id <> 3;

Greater Than, Less Than:

• > and < are used to test for values greater than or less than a given value.
• >= and <= are for greater than or equal to, and less than or equal to.
• Example:

SELECT * FROM sales WHERE amount > 500;
SELECT * FROM sales WHERE date <= '2023-01-01';

Logical Operators:

• AND is used to combine two conditions, both of which must be true.
• OR is used to combine two conditions, where at least one must be true.
• NOT negates a condition.
• Example:

SELECT * FROM employees WHERE department = 'Marketing' AND salary > 60000;
SELECT * FROM employees WHERE department = 'IT' OR department = 'HR';
SELECT * FROM employees WHERE NOT department = 'Sales';

Practice Exercises

1. Basic WHERE Clauses:
   • Retrieve all records from the ’employees’ table where the ‘department’ is ‘IT’.
   • Select all sales records from the ‘sales’ table where the ‘amount’ is greater than 1000.
2. Combining Conditions with AND, OR, NOT:
   • Find all employees in the ’employees’ table who are in the ‘Marketing’ department and have a salary greater than 50000.
   • Retrieve employees who are either in the ‘Sales’ department or have a salary less than 40000.
   • Get the list of products from the ‘products’ table that are not in the category ‘Electronics’.