Sql Using Where Clause For Filtering Complete Guide

Introduction to the WHERE Clause

The WHERE clause in SQL allows you to filter rows from a table based on specific conditions. This means that only the rows that meet the condition(s) specified in the WHERE clause will be returned in the result set.

Basic Syntax

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

Example Database Setup

Before we get started, let's imagine a simple database that includes a table named employees. Here’s how the table might look:

| employee_id | first_name | last_name | department | salary | |-------------|------------|------------|----------------|--------| | 1 | John | Doe | HR | 50000 | | 2 | Jane | Smith | Engineering | 70000 | | 3 | Michael | Johnson | Sales | 60000 | | 4 | Emily | Davis | Marketing | 55000 | | 5 | David | Brown | IT | 65000 |

Let's dive into some examples!

Example 1: Filter Rows Based on a Single Condition

Query: Select all employees who work in the Engineering department.

SELECT *
FROM employees
WHERE department = 'Engineering';

Result:

| employee_id | first_name | last_name | department | salary | |-------------|------------|-----------|------------|--------| | 2 | Jane | Smith | Engineering| 70000 |

In this example, the WHERE clause filters the rows where the department column matches 'Engineering'.

Example 2: Filter Rows Based on Multiple Conditions

Query: Select all employees who work in the IT department and have a salary greater than 62000.

SELECT *
FROM employees
WHERE department = 'IT' AND salary > 62000;

Result:

| employee_id | first_name | last_name | department | salary | |-------------|------------|-----------|------------|--------| | 5 | David | Brown | IT | 65000 |

Here, the AND operator is used to specify that both conditions must be true: department must be 'IT' and salary must be greater than 62000.

Example 3: Filter Rows Using OR Operator

Query: Select all employees who are either in the Sales or Marketing departments.

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

Result:

| employee_id | first_name | last_name | department | salary | |-------------|------------|-----------|------------|--------| | 3 | Michael | Johnson | Sales | 60000 | | 4 | Emily | Davis | Marketing | 55000 |

This query uses the OR operator to check if the department column is either 'Sales' or 'Marketing'.

Example 4: Filter Rows with Inequality and Range Conditions

Query: Select all employees whose salary is less than 58000.

SELECT *
FROM employees
WHERE salary < 58000;

Result:

| employee_id | first_name | last_name | department | salary | |-------------|------------|-----------|------------|--------| | 1 | John | Doe | HR | 50000 | | 4 | Emily | Davis | Marketing | 55000 |

In this case, the WHERE clause checks the condition where salary is less than 58000.

Example 5: Filter Rows Using Pattern Matching

Query: Select all employees whose last name ends with 'son'.

SELECT *
FROM employees
WHERE last_name LIKE '%son';

Result:

| employee_id | first_name | last_name | department | salary | |-------------|------------|-----------|------------|--------| | 1 | John | Doe | HR | 50000 | | 3 | Michael | Johnson | Sales | 60000 |

Here, the LIKE keyword along with %son (where % is a wildcard for any sequence of characters) is used to find all last names ending with 'son'.

Example 6: Filter Rows Using BETWEEN Keyword

Query: Select all employees with salaries between 55000 and 65000 (inclusive).

SELECT *
FROM employees
WHERE salary BETWEEN 55000 AND 65000;

Result:

| employee_id | first_name | last_name | department | salary | |-------------|------------|-----------|------------|--------| | 4 | Emily | Davis | Marketing | 55000 | | 3 | Michael | Johnson | Sales | 60000 | | 5 | David | Brown | IT | 65000 |

The BETWEEN keyword retrieves values within the range specified. Note that it includes the boundary values.

Example 7: Filter Rows Using IN Keyword

Query: Select all employees who work in either the HR or IT departments.

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

Result:

| employee_id | first_name | last_name | department | salary | |-------------|------------|-----------|------------|--------| | 1 | John | Doe | HR | 50000 | | 5 | David | Brown | IT | 65000 |

The IN keyword allows checking against multiple possible values. In this example, it filters out employees in 'HR' and 'IT'.

Example 8: Filter Rows Based on Null Values

Query: Assume there's an additional column manager_id which can be null or not. Select all employees without a manager (null manager_id).

SELECT *
FROM employees
WHERE manager_id IS NULL;

Result: (Assuming no null values in this dataset)

Since our setup doesn’t include a manager_id column with null values, the result would be empty. But if there were entries with null manager_id, they would be returned by this query.

Example 9: Combine Multiple Conditions with Parentheses

Query: Select all employees who work in either Engineering or IT departments and have a salary greater than 60000.

SELECT *
FROM employees
WHERE (department = 'Engineering' OR department = 'IT') AND salary > 60000;

Result:

| employee_id | first_name | last_name | department | salary | |-------------|------------|-----------|------------|--------| | 2 | Jane | Smith | Engineering| 70000 | | 5 | David | Brown | IT | 65000 |

Parentheses are used to specify the order in which conditions are evaluated. Here, we combine two separate conditions: checking the departments and then checking the salary.

Conclusion

The WHERE clause is a powerful tool for filtering rows in SQL. You can use comparison operators (=, <, >, <=, >=, <>), logical operators (AND, OR), pattern matching (LIKE), range values (BETWEEN), specific values (IN), and even check for NULL using the IS NULL condition.

Practice using different combinations and conditions to refine your SQL queries effectively for retrieving required data.