MySQL WHERE Clause - Display MySQL Table Data Conditionally

The MySQL WHERE Clause is a powerful and widely used keyword that allows you to set a conditional filter on your queries. The WHERE clause can be used on the SELECT, UPDATE, and DELETE statements.

Suppose you have a long guest list for an international conference and you want to see only the guests from Canada. In this case, “Only the guests from Canada” is a condition. Like the powerful language it is, MySQL makes your job easier over here.

If you have a table for the above, the MySQL WHERE clause combined with the SELECT statement, eases your work by finding out the guests from Canada.

MySQL WHERE Clause Syntax

Depending on which statement you use it with, the WHERE clause has the following syntax:

With the SELECT Statement –

SELECT expression FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

With the UPDATE Statement –

UPDATE table_name SET column1 = value1, column2 = value2, …  WHERE condition;Code language: SQL (Structured Query Language) (sql)

With the DELETE Statement –

DELETE FROM table_name WHERE condition;Code language: SQL (Structured Query Language) (sql)

Examples of MySQL WHERE

Now let us dive into a few examples. Consider the following ConferenceGuests table.

1. WHERE Clause with the SELECT Statement

Suppose you want to select all the guests from Canada. Then, you will use the following query:

SELECT * FROM ConferenceGuests WHERE Country='Canada';Code language: SQL (Structured Query Language) (sql)

You will get the following output –

It is important to note here that SQL requires you to put single or double quotes around text values.

Now, what if you want to be a bit more specific and find the guest whose ID is 14? You will use the following query for it:

SELECT * FROM ConferenceGuests WHERE ID=14;Code language: SQL (Structured Query Language) (sql)

The output will be –

Unlike text values, numerical values do not need single or double-quotes.

Using Where clause with multiple conditions

We can also specify multiple conditions using the WHERE Clause. How about finding guests whose ID is greater than 10 and are from the United States? We will do this by using the following query:

SELECT * FROM ConferenceGuests WHERE ID>10 AND Country='United States';Code language: SQL (Structured Query Language) (sql)

The output will be as follows –

2. WHERE Clause with the UPDATE Statement

As you can see in the ConferenceGuests table, the entry in the State column for Sakura is set to NULL.

Let’s say Sakura informs you that the name of her state is Akita and she would like it if you added it to the table. How would you do that?

The UPDATE statement combined with the MySQL WHERE clause can help you achieve this by filtering to Sakura’s record.

To do this, we can either have a condition where Name = ‘Sakura’ or ID = 16.

I would highly recommend using ID as the condition instead of Name, because while using bigger tables, you may encounter many people with the same name. ID would generally be unique.

UPDATE ConferenceGuests SET State=‘Akita’ WHERE ID=16;Code language: SQL (Structured Query Language) (sql)

Let us follow this statement by doing –

SELECT * FROM ConferenceGuests;Code language: SQL (Structured Query Language) (sql)

So that we can see the output as follows:

As you can see, Sakura’s record does not have a NULL value in the State column anymore as it was updated to ‘Akita’.

3. WHERE Clause with the DELETE Statement

Suppose at the last minute, Robert from Queensland, Australia informs you that he won’t be able to attend the conference and that you may erase his entry from the guest list.

The DELETE statement combined with the MySQL WHERE clause can help you achieve this. Again, we can either have a condition where Name = ‘Robert’ or ID = 9. As I mentioned earlier, it is better to use ID = 9 as the condition so we will go ahead with that –

DELETE FROM ConferenceGuests WHERE ID=9;Code language: SQL (Structured Query Language) (sql)

Let us follow this statement by doing –

SELECT * FROM ConferenceGuests;Code language: SQL (Structured Query Language) (sql)

The output will be as follows –

If you see, Robert’s entry has now been deleted.

A Summary of Different Operators For The WHERE Clause

Operator	Description	Example
>	Greater than	SELECT * FROM ConferenceGuests WHERE ID>10;
>=	Greater than and equal to	SELECT * FROM ConferenceGuests WHERE ID>=10;
<	Less than	SELECT * FROM ConferenceGuests WHERE ID<10;
<=	Less than and equal to	SELECT * FROM ConferenceGuests WHERE ID<=10;
!= or <>	Not equal to	SELECT * FROM ConferenceGuests WHERE ID!=10;
=	Equal to	SELECT * FROM ConferenceGuests WHERE ID=10;
IN	Value is from a given list of values	SELECT * FROM ConferenceGuests WHERE ID IN (2, 3, 5);
NOT IN	Value is not from a given list of values	SELECT * FROM ConferenceGuests WHERE Country NOT IN (‘India’, ‘Japan’);
LIKE	Search for a pattern	SELECT * FROM ConferenceGuests WHERE Name LIKE ‘Adi%’;
BETWEEN	Value is between a certain range	SELECT * FROM ConferenceGuests WHERE ID BETWEEN (5, 10);

Conclusion

The WHERE clause is a very important fundamental concept of MySQL. I would encourage you to check out the references for further reading.

References

JournalDev Article on WHERE Clause.
MySQL documentation on WHERE Clause.