MySQL WHERE Clause

MySQL WHERE clause is used to filter data from a table based on specified conditions and return it in the result set. It is generally used in SELECT, INSERT, UPDATE, and DELETE statements to work on specific data.

It follows the FROM Clause in a SELECT statement and precedes any ORDER BY or GROUP BY Clauses. Within the WHERE clause, you can specify one or more conditions that the data must meet to be included in the result.

Conditions can involve comparisons (e.g., equal to, not equal to, greater than, less than), logical operators (AND, OR), and other expressions.

Syntax

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

Demo MySQL Database

In this tutorial on MySQL WHERE clause, we will use the following table in examples.

customer_id	name	city	age
1	John Doe	New York	30
2	Jane Smith	Los Angeles	25
3	Robert Johnson	New York	35
4	Alice Brown	Chicago	28
5	Charlie Wilson	Los Angeles	40

To create this table on your system, write the following MySQL queries:

MySQL

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50),
    age INT
);

INSERT INTO customers VALUES
    (1, 'John Doe', 'New York', 30),
    (2, 'Jane Smith', 'Los Angeles', 25),
    (3, 'Robert Johnson', 'New York', 35),
    (4, 'Alice Brown', 'Chicago', 28),
    (5, 'Charlie Wilson', 'Los Angeles',40);

WHERE Clause Examples

Let’s see some examples of where clause and understand it’s working in MySQL :

Example 1: Filtering by a Single Condition

Let’s find out the records of all those customers who live in “New York“.

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

Output:

Explanation:

Here all the records in the output, that have the city name “New York” in common hence fulfilling the condition.

Example 2: WHERE Clause with AND Operator

Let’s find out all those customers who live in ‘Los Angeles‘ with ages less than 30.

SELECT * FROM customers WHERE city = 'Los Angeles' AND age < 30;

Output:

Explanation:

Here only one record is in output because Jane Smith is the only person who lives in Los Angeles and has an age less than 30.

Example 3: WHERE Clause with OR Operator

Let’s find out all customers who either live in ‘New York‘ or their age greater than 35.

SELECT * FROM customers WHERE city = 'New York' OR age > 35;

Output:

Explanation:

Here two records are in output because 1st Bob Johnson satisfies one condition of living in New York and Charlie Wilson satisfies the condition of age.

Example 4: WHERE Clause with Comparison Operators

Let’s find out all customers who are older than 30 years.

SELECT * FROM customers WHERE age > 30;

Output

Explanation:

Here the record is filtered out based on the age of those persons greater than 30. Hence doing comparison simple.

Example 5: WHERE Clause with LIKE Operator

Let’s find out all customers whose names start with the “J” letter.

SELECT * FROM customers WHERE name LIKE 'J%';

Output:

Explanation:

The meaning of % is pattern matching starting with J, and then going on. There are only two people whose names start with J.

Example 6: WHERE Clause with IN Clause

Let’s find out all the customers whose city name is NewYork, Chicago

SELECT * FROM customers WHERE city IN ('New York', 'Chicago');

Explanation:

Outputs the record who all customers living in Chicago, New York.

Example 7: WHERE Clause with ORDER BY Clause

Let’s find out all records of customers who belong to “NewYork” but the condition is the records must be in descending order by age.

SELECT * FROM customers WHERE city = 'New York' ORDER BY age DESC;

Output:

Explanation:

Retrieve customers from New York and order them by age in descending order.

Operators in WHERE Clause

The following operators can be used in the WHERE clause:

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

Key Takeaways About MySQL WHERE Clause

• WHERE Clause helps to find any kind of data from the database with the help of specified conditions.
• WHERE clause in MySQL acts as a filter, allowing you to pull out specific data from a sea of information.
• It precedes other clauses like ORDER BY or GROUP BY Clauses.
• WHERE Clause uses operators like =,>,< etc, to add conditions for filtering data.