Where Clause in MySQL

Introduction

The WHERE clause serves as a fundamental tool for refining data selection in MySQL. It allows users to narrow down their queries to extract specific information that meets defined conditions. Without the WHERE clause, database queries would retrieve indiscriminate and voluminous amounts of data, leading to information overload and diminished efficiency. With the WHERE clause, users can exercise fine-grained control over the data they retrieve, improving the precision and relevance of the results obtained.

Whether it's filtering data based on specific column values, combining conditions with logical operators, or utilizing comparison operators for precise comparisons, the WHERE clause empowers users to extract the desired information from large datasets. By effectively utilizing the WHERE clause, database administrators, developers, and analysts can streamline their data retrieval processes, enhance performance, and gain valuable insights from their MySQL databases.

What is WHERE Clause in MySQL?

In MySQL, the WHERE clause is used in conjunction with the SELECT statement to filter rows from a table based on specific conditions. It allows you to retrieve only the data that meets the specified criteria, resulting in more targeted and meaningful results. The WHERE clause acts as a logical filter that limits the rows returned by a query, ensuring that only the relevant data is included. It can be used with various operators and keywords to perform complex filtering operations. The UPDATE and DELETE commands are also covered by the WHERE clause in addition to SELECT, just adding to its importance in SQL.

WHERE Clause Syntax

The basic syntax of the WHERE clause in MySQL is as follows:

The SELECT statement is followed by a list of columns to retrieve data from and the FROM keyword specifies the table to query. The WHERE keyword is used to introduce the conditions that must be met. The conditions are defined after the WHERE keyword and can involve one or more columns and operators. These conditions generally include comparison operators. We will look at how these operators are used with the WHERE clause in the following section.

WHERE Clause Combined with – COMPARISON Operators

The WHERE clause combined with comparison operators allows you to perform precise comparisons to filter data based on specific conditions. Here are some commonly used comparison operators:

1. Equal To (=)

The Equal to Operator filter rowsthath satisfies equality between the two quantities specified in the WHERE clause using '='.

Let's consider a sample database for an online bookstore. We have a table named "books" with the following columns: "book_id", "title", "author", "price", and "stock".

Here's an example dataset:

The query to retrieve books with a price equal to 300 rupees will be:

Output:

There was only one book present with a price of ₹300 and was returned.

2. Greater than and Less than (>, <, >=, <=)

These operators simply compare the two quantities according to the particular operator used, and the rows which satisfy the criteria are selected.

For example, to select books with a price less than ₹320, the query will be:

The output hence will be:

The above books satisfied the above-mentioned criteria of a price of more than ₹320.

3. Not Equal To (<>)

The Not Equal To operator will be True if the two operands are not equal in value. The rows satisfying this criterion will hence be selected.

For example, to select books whose author is not George Orwell, the query will be:

Output:

Many such conditions may also be used in conjunction using logical operators like AND, OR, NOT etc. We'll see examples for each in the following sections.

WHERE Clause Combined with – AND LOGICAL Operator

The WHERE clause combined with the AND logical operator allows you to apply multiple conditions to filter the rows over which all the specified conditions are true simultaneously.

In the above example database, suppose we want to retrieve books that are priced higher than or equal to ₹ 300 and have a stock quantity less than or equal to 10. We can use the following query:

Output:

The query retrieves books that meet both the above-mentioned conditions. In this case, the output includes two such books.

WHERE Clause Combined with – OR LOGICAL Operator

The WHERE clause combined with the OR logical operator allows you to specify multiple conditions where at least one condition must be true for a row to be included in the result set.

Let's say we want to retrieve books written by Jane Austen or books with a price higher than ₹320. We can use the following query:

Output:

The query retrieves books that either match the author name 'Jane Austen' or have a price higher than ₹320. In this case, the output includes three such books.

WHERE Clause Combined with – IN Keyword

The IN keyword in the WHERE clause allows you to specify multiple values for a column, and the query will retrieve rows where the column value matches any of the specified values.

Suppose we want to retrieve books written by Harper Lee or George Orwell. We can use the following query:

Output:

The query retrieves books where the author's name matches either 'Harper Lee' or 'George Orwell'. In this case, there were two such books.

WHERE Clause Combined with – NOT IN Keyword

The NOT IN keyword in the WHERE clause allows you to exclude rows that match any of the specified values.

Let's say we want to exclude books written by Jane Austen or George Orwell. We can use the following query:

Output:

The query retrieves books where the author's name does not match 'Jane Austen' or 'George Orwell'. In this case, the output includes three such books.