MySQL WHERE Clause – Display MySQL Table Data Conditionally

Mysql Where

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:

  1. With the SELECT Statement – 
SELECT expression FROM table_name WHERE condition;
  1. With the UPDATE Statement – 
UPDATE table_name SET column1 = value1, column2 = value2, …  WHERE condition;
  1. With the DELETE Statement –
DELETE FROM table_name WHERE condition;

Examples of MySQL WHERE

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

Conferenceguests Table
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';

You will get the following output – 

Where Single Text Condition

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;

The output will be – 

Where Single Numeric Condition

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';

The output will be as follows – 

Where Multiple Conditions

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;

Let us follow this statement by doing – 

SELECT * FROM ConferenceGuests;

So that we can see the output as follows: 

Where Update

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;

Let us follow this statement by doing – 

SELECT * FROM ConferenceGuests;

The output will be as follows – 

Where Delete

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


A Summary of Different Operators For The WHERE Clause

OperatorDescriptionExample
Greater thanSELECT * FROM ConferenceGuests WHERE ID>10;
>=Greater than and equal toSELECT * FROM ConferenceGuests WHERE ID>=10;
Less thanSELECT * FROM ConferenceGuests WHERE ID<10;
<=Less than and equal toSELECT * FROM ConferenceGuests WHERE ID<=10;
!= or <>Not equal toSELECT * FROM ConferenceGuests WHERE ID!=10;
=Equal toSELECT * FROM ConferenceGuests WHERE ID=10;
INValue is from a given list of valuesSELECT * FROM ConferenceGuests WHERE ID IN (2, 3, 5);
NOT INValue is not from a given list of valuesSELECT * FROM ConferenceGuests WHERE Country NOT IN (‘India’, ‘Japan’);
LIKESearch for a patternSELECT * FROM ConferenceGuests WHERE Name LIKE ‘Adi%’;
BETWEENValue is between a certain rangeSELECT * 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