MySQL WHERE

The WHERE clause in MySQL is a powerful and essential component of SQL queries. It allows you to filter the rows retrieved from a table based on specified conditions. This clause is commonly used in conjunction with SELECT, UPDATE, DELETE, and INSERT statements to narrow down the results or specify the affected rows.

Here’s a breakdown of how the WHERE clause works:

Syntax

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

SELECT: Specifies the columns you want to retrieve.
FROM: Specifies the table from which to retrieve the data.
WHERE: Specifies the conditions that must be met for a row to be included in the result set.

Examples

Simple WHERE clause:

SELECT * FROM employees
WHERE department = 'IT';

This query retrieves all columns from the “employees” table where the department is ‘IT’.

Multiple conditions:

SELECT * FROM products
WHERE price > 50 AND stock_quantity > 0;

Retrieves all products with a price greater than 50 and a positive stock quantity.

Using Comparison Operators:

SELECT * FROM orders
WHERE order_date >= '2023-01-01';

Retrieves orders placed on or after January 1, 2023.

Using LIKE for pattern matching:

SELECT * FROM customers
WHERE last_name LIKE 'Sm%';

Retrieves customers with last names starting with “Sm”.

Combining AND, OR, and Parentheses:

SELECT * FROM products
WHERE (category = 'Electronics' AND price > 100) 
OR (category = 'Clothing' AND stock_quantity > 10);

Retrieves products that are either expensive electronics or well-stocked clothing.

Checking for NULL values:

SELECT * FROM orders
WHERE shipping_address IS NULL;

Retrieves orders with no specified shipping address.

IN operator for multiple values:

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

Retrieves employees working in the HR, Finance, or Admin departments.

BETWEEN operator for a range:

SELECT * FROM products
WHERE price BETWEEN 50 AND 100;

Retrieves products with prices between 50 and 100.

The WHERE clause is flexible and allows you to tailor your queries to specific criteria, making it a fundamental tool for managing and analyzing data in MySQL databases.