SQL WHERE

The WHERE clause is the primary way to filter data in SQL. Without it, a SELECT statement would return every single row in a table, which is rarely what you want in a production environment. By using WHERE, you can narrow down your results to find specific records, such as users who signed up today, products that are out of stock, or high-value transactions.

Basic Syntax of WHERE

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

• column1, column2, ...: The specific fields you want to see in your results.
• table_name: The source table where the data lives.
• condition: A logical expression that evaluates to True, False, or Unknown. Only rows where the condition is True are returned.

Key Features of the WHERE Clause

1. Filtering with Comparison Operators

Comparison operators allow you to filter numbers, strings, and dates. They are the building blocks of most SQL queries.

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

SELECT * FROM employees WHERE salary > 50000;

This query pulls the full record for any employee earning more than 50,000. In a real-world HR system, this is how you might generate a list for a "Senior Staff" report.

2. Using AND / OR Operators

To create more complex logic, you can combine multiple conditions.

• AND: Requires all conditions to be met.
• OR: Requires at least one condition to be met.

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 40000;

This is strict: it finds employees who are in Sales and also earn a decent salary.

SELECT * FROM employees
WHERE department = 'Sales' OR department = 'HR';

This is broader: it returns anyone who works in either of those two departments.

3. Using LIKE Operator

The LIKE operator is used for "fuzzy" searching or pattern matching. It’s perfect for search bars where a user might only know part of a name.

• %: Represents a wildcard for zero, one, or multiple characters.
• _: Represents exactly one single character.

SELECT * FROM employees WHERE name LIKE 'J%';

This finds names like 'John', 'Jane', or 'Justin'.

SELECT * FROM employees WHERE name LIKE '_o%';

This finds names where the second letter is 'o', such as 'Robert' or 'Tony'.

4. Using IN Operator

The IN operator is a shorthand for multiple OR conditions. It makes your code much cleaner and easier to read.

SELECT * FROM employees WHERE department IN ('Sales', 'HR', 'IT');

Instead of writing department = 'Sales' OR department = 'HR'..., you can simply provide a list. This is also very useful when working with subqueries.

5. Using BETWEEN Operator

The BETWEEN operator selects values within a specific range. It is inclusive, meaning the start and end values are included in the results.

SELECT * FROM employees WHERE salary BETWEEN 30000 AND 60000;

This is much more readable than using salary >= 30000 AND salary <= 60000.

6. Using IS NULL / IS NOT NULL

In SQL, NULL represents missing or unknown data. You cannot use = NULL because NULL is not a value; it's a state.

SELECT * FROM employees WHERE department IS NULL;

This finds employees who haven't been assigned to a department yet.

SELECT * FROM employees WHERE department IS NOT NULL;

This filters out any records where the department field is empty.

7. Using Logical Operators

The NOT operator allows you to reverse any condition. It is frequently paired with IN, LIKE, and BETWEEN.

SELECT * FROM employees WHERE department NOT IN ('Sales', 'HR');

This effectively retrieves everyone except those in Sales or HR.

Example Queries

1. Get Employees with Salary Greater Than 50,000

Used for filtering high-earners for payroll audits.

SELECT * FROM employees WHERE salary > 50000;

2. Get Employees from 'Sales' or 'IT' Department

A clean way to target specific internal teams.

SELECT * FROM employees WHERE department IN ('Sales', 'IT');

3. Find Employees with First Name Starting with 'A'

Useful for alphabetical directories or user searches.

SELECT * FROM employees WHERE first_name LIKE 'A%';

4. Find Employees Whose Salary is Between 40,000 and 70,000

Perfect for analyzing mid-level management compensation.

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 70000;

5. Find Employees with No Department Assigned

Identifies data entry errors or new hires awaiting placement.

SELECT * FROM employees WHERE department IS NULL;

Summary

The WHERE clause is the engine behind data filtering in SQL. Mastery of this clause allows you to write efficient queries that only return the data you actually need, saving system resources and making your applications faster. By combining comparison operators with logical operators like AND, OR, and NOT, you can handle almost any data retrieval requirement a modern application might have.