datalitico.com

Menu

Using the WHERE Clause to Filter Data

In SQL, the WHERE clause is used to filter data retrieved from a table based on specific conditions. It allows you to specify criteria that the rows must meet in order to be included in the result set. The WHERE clause is typically used in conjunction with the SELECT statement.

Basic Syntax

The basic syntax of a SELECT statement with the WHERE clause is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • column1, column2, …: Specifies the columns to retrieve from the table.
  • table_name: Specifies the table from which to retrieve the data.
  • condition: Specifies the criteria that the selected rows must meet.

What are Conditions?

Conditions in the WHERE clause are used to evaluate the values in the rows. The conditions can be simple or complex, and they can involve comparisons, logical operators (AND, OR, NOT), and functions. Some examples of conditions are:

  • column_name = value: Retrieves rows where the value in the column equals the specified value.
  • column_name > value: Retrieves rows where the value in the column is greater than the specified value.
  • column_name BETWEEN value1 AND value2: Retrieves rows where the value in the column falls within a specified range.
  • column_name LIKE pattern: Retrieves rows where the value in the column matches a specified pattern.
  • column_name IS NULL: Retrieves rows where the value in the column is null.

Using Multiple Conditions

You can combine multiple conditions in the WHERE clause using logical operators. Examples of logical operators are:

  • AND: Retrieves rows that satisfy both conditions.
  • OR: Retrieves rows that satisfy either condition.
  • NOT: Retrieves rows that do not satisfy the condition.

Here are some examples that demonstrate the usage of the WHERE clause to filter data

/* Retrieve customers 
from the "customers" table 
with a specific city */
SELECT *
FROM customers
WHERE city = 'New York';

/* Retrieve orders 
from the "orders" table 
with a specific status 
and a total greater than a certain value */
SELECT *
FROM orders
WHERE status = 'Completed' AND total > 1000;

/* Retrieve products 
from the "products" table
that belong to a specific category */
SELECT *
FROM products
WHERE category_id = 5;

/* Retrieve employees 
from the "employees" table 
that were hired in a specific year */
SELECT *
FROM employees
WHERE YEAR(hire_date) = 2022;

By using the WHERE clause, you can apply specific conditions to filter the data retrieved from a table. This allows you to narrow down the result set and focus on the data that meets your specific criteria. The WHERE clause is a powerful tool for extracting meaningful insights from your database.

Scroll to Top