MySQL Where Clause in Detail

Sometimes, we want to delete or update a few tuples based on some conditions. Sometimes, we want to select some tuples based on some condition instead of selecting all the tuples. Thus, we need to use some conditions in the SQL queries. Here, we will discuss, how where clauses can be associated with the SQL queries. We will start a discussion with the following table.

To prepare this table, insert some data using the following SQL queries.

insert into books values ('C Programming Language', 2, '9780131103627', 'Brian W. Kernighan, Dennis M. Ritchie', 'Paperback', 'Prentice Hall', 'April 1988', 67.00);

 

insert into books values ('Java: A Beginners Guide', 7, '9781259589317', 'Herbert Schildt', 'Paperback', 'McGraw-Hill Education', 'October 2017', 40.00);

 

insert into books values ('Introduction to Algorithms', 3, '9780262033848', 'Thomas H. Cormen, Charles E. Leiserson, Ronald L. Rivest, Clifford Stein', 'Hardcover', 'The MIT Press', 'July 2009', 99.00);

 

insert into books values ('Operating Systems', 2, '9780985673529', 'Thomas Anderson, Michael Dahlin', 'Paperback', 'Recursive Books', 'August 2014', 72.00);

Where Clause and Select Queries

If we run select * from books; query, the query returns all the tuples of the table. If we want to apply a filter like this, we want to see the names of all the books that have Paperback binding, we can use the where clause mentioning the condition. The query is shown below:

Multiple conditions can also be used in SQL queries, at this time the conditions are to be added by and. In the following example, we have shown how multiple queries can be added.

Where Clause and Update Queries

At the time of the updation of tuples in a table, if we do not mention any condition, all the tuples are updated in the table with the new values of the attributes. To update selective tuples based on some condition where clause needs to be associated with an update query.

Let us consider that the price of all the books is to be increased by 10%. We can write the update query as follow:

If you want to update the price of the C Programming Language book, you can run the following query:

Where Clause and Delete Queries

At the time of deletion of tuples from a table, if we do not mention any condition, all the tuples are deleted from the table. It works similarly to a truncate query. To delete tuples based on some condition where clause needs to be associated with delete query.

In this context, if you want to delete the books if the price is greater than $85, you can run the following query.

You can also add multiple conditions in the update as well as in delete queries.

Happy Exploring!