Where Clause

The WHERE clause is used to filter records based on specific conditions. It allows you to extract only those records that fulfill a specified condition.

Basic Syntax

The basic syntax of the WHERE clause is:

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

Examples

1. Simple Comparison

SELECT *
FROM employees
WHERE department = 'Sales';

This query retrieves all columns for employees in the Sales department.

2. Using Numeric Comparisons

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

This query finds employees with a salary greater than 50,000.

3. Using Multiple Conditions with AND

SELECT product_name, unit_price, units_in_stock
FROM products
WHERE category = 'Electronics' AND unit_price < 100;

This query finds electronic products priced under 100.

4. Using Multiple Conditions with OR

SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Marketing' OR department = 'Sales';

This query retrieves employees from either the Marketing or Sales departments.

5. Using the IN Operator

SELECT product_name, supplier_id
FROM products
WHERE supplier_id IN (1, 3, 5);

This query finds products from suppliers with IDs 1, 3, or 5.

6. Using the BETWEEN Operator

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

This query finds orders placed in the year 2023.

7. Using the LIKE Operator

SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%@company.com';

This query finds employees with a company email address.

8. Using the IS NULL Operator

SELECT product_name, description
FROM products
WHERE description IS NULL;

This query finds products that don't have a description.

9. Combining Multiple Conditions

SELECT first_name, last_name, department, salary
FROM employees
WHERE (department = 'Sales' OR department = 'Marketing')
  AND salary > 60000
  AND hire_date < '2022-01-01';

This complex query finds Sales or Marketing employees with a salary over 60,000 who were hired before 2022.

The WHERE clause is a powerful tool for filtering data and is often used in combination with other SQL clauses to create precise and efficient queries.