Filtering Data with WHERE Clause in SQL | SQL WHERE Tutorial

Introduction

In real-world databases, you often need to retrieve only specific records instead of fetching all the data. The WHERE clause in SQL allows you to filter rows based on specific conditions. Using the WHERE clause makes your queries powerful, efficient, and targeted.

In this section, you will learn the syntax, operators, and examples of using the WHERE clause to filter data effectively.

What is the WHERE Clause?

The WHERE clause is used to specify a condition while retrieving data from a table. Only the rows that satisfy the condition will be included in the result set.

Basic Syntax:

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

SELECT: Identifies the columns you want to retrieve.
FROM: Identifies the table from which to pull the data.
WHERE: Defines the condition to filter the rows.

Example: Simple WHERE Clause

Suppose you have an employees table and want to retrieve only employees in the "Sales" department.

SELECT first_name, department
FROM employees
WHERE department = 'Sales';

Result: Only employees whose department is "Sales" will be shown.

Common Operators Used with WHERE

You can use various comparison operators in the WHERE clause to create different types of conditions:

Using Multiple Conditions

You can combine multiple conditions using AND, OR, and NOT.

AND Example:

Fetch employees who are in "Sales" and have a salary greater than 50,000:

SELECT first_name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 50000;

OR Example:

Fetch employees who are either in "Marketing" or in "Finance":

SELECT first_name, department
FROM employees
WHERE department = 'Marketing' OR department = 'Finance';

NOT Example:

Fetch employees who are not in the "HR" department:

SELECT first_name, department
FROM employees
WHERE NOT department = 'HR';

Filtering with BETWEEN

Use BETWEEN to filter values within a specific range.

Example:

Fetch employees with a salary between 40,000 and 80,000:

SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 80000;

Filtering with IN

Use IN to check if a value matches any value in a list.

Example:

Fetch employees based in either "New York", "Chicago", or "Los Angeles":

SELECT first_name, city
FROM employees
WHERE city IN ('New York', 'Chicago', 'Los Angeles');

Filtering with LIKE (Pattern Matching)

Use LIKE for pattern matching, especially with text fields.

Example:

Fetch employees whose first names start with "J":

SELECT first_name
FROM employees
WHERE first_name LIKE 'J%';

NULL Handling in WHERE Clause

Sometimes data fields can be empty (NULL). Use IS NULL or IS NOT NULL to filter such records.

Example:

Fetch employees who do not have a manager assigned:

SELECT first_name
FROM employees
WHERE manager_id IS NULL;

Conclusion

The WHERE clause is essential for filtering and retrieving only the data you need. Mastering different conditions, operators, and combinations of WHERE clauses is key to writing effective SQL queries.