MySQL WHERE Clause

Last Updated: 21 June, 2023

The WHERE clause in MySQL is used to filter data based on a specific condition or set of conditions. It allows us to specify a condition that must be met for a row to be included in the result set of a query.

The basic syntax of the WHERE clause is as follows:

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

Here, column1, column2, ... refers to the columns we want to select or modify, table_name is the name of the table we are working with, and condition is the filtering condition.

Here are some examples of how the WHERE clause can be used:

 SELECT *
 FROM customers
 WHERE city = 'New York';

The WHERE clause is commonly used in SELECT, UPDATE, and DELETE statements.

1. SELECT statement:

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

   For example, to select all rows from a table called "customers" where the age is greater than 30:

    SELECT *
    FROM customers
    WHERE age > 30;
   

2. UPDATE statement:

    UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    WHERE condition;
   

   For example, to update the "email" column of a table called "customers" for rows where the age is greater than 30:

    UPDATE customers
    SET email = 'newemail@example.com'
    WHERE age > 30;
   

3. DELETE statement:

    DELETE FROM table_name
    WHERE condition;
   

   For example, to delete all rows from a table called "customers" where the age is less than 18:

    DELETE FROM customers
    WHERE age < 18;
   

Some common operators used in the WHERE clause are:

• =: Equal to
• <> or !=: Not equal to
• <: Less than
• >: Greater than
• <=: Less than or equal to
• >=: Greater than or equal to
• LIKE: Pattern matching (wildcards can be used)
• IN: Match any value in a list
• BETWEEN: Between a range of values
• AND: Logical AND
• OR: Logical OR
• NOT: Negates a condition

Here are some examples of common operators with WHERE caluse.

Using logical operators:

 SELECT *
 FROM products
 WHERE category = 'Electronics' AND price > 500;

Using the LIKE operator for pattern matching:

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