SQL Where Clause Example

1. Introduction

In this article, we will look at the SQL Where Clause. The Update, Delete, and Select statements have an optional Where clause.

2. Where in Relational Algebra

The condition we put to filter records is the “where” clause in Relational Algebra. The condition parameter is :

σ _(Cond)(Relation Name)

Here,

3. SQL Where Clause

The “where” clause is optional. Data Manipulation Language constructs like Select, Update, and Delete use it. Although optional it plays a very important role in getting data across multiple tables.

The basic syntax of a where clause is as follows:

Select /Update/Delete …
[WHERE condition1 [AND [OR]] condition2…..

Condition1, condition2, etc are the filter conditions.

Should we miss the where clause in the Update or Delete, the entire table will be updated or deleted. This leads to a lot of data loss.

4. Where Clause optimization in MySQL

MySQL engine does optimizations internally to make queries run faster and more efficiently. Incorrect or inefficiently written queries especially on large datasets can cause serious performance problems for both the database as well as the application consuming the data. Hence, optimization of where clause is crucial.  Some of the basic optimizations are as follows:

• Remove extra parentheses.
•  Values that can be folded are folded constantly.
• Remove redundant constant conditions.
• Evaluate constant expressions used by indexes only once.
• Remove out-of-range and constant numeric types of values. They are not auto-cast to the higher type anymore.
• MySQL can detect invalid statements and stop retrieval to give back no rows.
• Merge the Where and Having if there is no GROUP BY.
• MySQL also rearranges the statements in the where clause to get faster execution by skipping unnecessary rows.

These are just some of the optimizations that MySQL engine does. The complete details of optimization are available here.

Users can also perform their own optimizations using the “Explain” statement in MySQL. How to use the “Explain” statement is given here. Using explain plan for Query tuning is in this tutorial.

4.1 Setup for examples

Forgoing through the examples related to the Where clause, we will consider the database called “EmployeesDB”. This is a sample database by MySQL. The structure of the employees is available in this document. For running the queries, we will use the MySQL Workbench. The documentation for MySQL Workbench is available here.

5. Examples with Where Clause

We will see various examples with the where clause. The examples are given for the select clause but the syntax and examples pertaining to the Where clause is applicable to Delete and Update Queries as well.  

5.1 Simple Where Clause examples

We can use the Where Clause with just a hard-coded value and an equals sign. This can return either no or more records.

Single Record Query:

 SELECT * FROM DEPARTMENTS WHERE DEPT_NAME = 'MARKETING';

Multiple records:

 SELECT * FROM SALARIES WHERE EMP_NO = 10001;

No records:

SELECT * FROM DEPARTMENTS 
WHERE DEPT_NAME = 'APPLICATION DEVELOPMENT';

5.2 With Operators: <,>,!=

We can use operators like <, <=,>,>=, != to filter records. Given below are examples of <,> and !=

Queries:

SELECT * FROM SALARIES WHERE SALARY > 155700;

SELECT * FROM SALARIES WHERE SALARY < 39000;

SELECT COUNT(*),TITLE FROM TITLES WHERE TITLE != 'STAFF'
GROUP BY TITLE;

5.3 Where clauses with IN and NOT IN.

To check records based on several values we can use the “IN” and “Not IN” operators. Below are simple examples of the same. Subqueries also use these operators.

SELECT COUNT(EMP_NO),TITLE FROM TITLES 
WHERE TITLE IN ('SENIOR ENGINEER', 'ENGINEER', 'ASSISTANT ENGINEER')
GROUP BY TITLE;

SELECT COUNT(EMP_NO),TITLE FROM TITLES 
WHERE TITLE NOT IN ('SENIOR ENGINEER', 'ENGINEER', 'ASSISTANT ENGINEER')
GROUP BY TITLE;

SELECT * FROM EMPLOYEES WHERE EMP_NO IN
(SELECT DISTINCT EMP_NO FROM TITLES WHERE TITLE IN ('Staff','Technique Leader'))
LIMIT 20;

5.4 Where clauses with AND, OR and LIKE

The operators AND,OR etc are used to add multiple filters or conditions. LIKE is used when an incomplete pattern needs to be used. AND and OR are extensively used when joining multiple tables. Given below are simple examples of using these operators.

SELECT DISTINCT EMP_NO FROM SALARIES 
WHERE SALARY = 39000
LIMIT 20;

SELECT COUNT(*),DEPT_NO FROM DEPT_EMP 
WHERE DEPT_NO = 'D002' OR DEPT_NO = 'D008' 
GROUP BY DEPT_NO;

SELECT * FROM EMPLOYEES 
WHERE HIRE_DATE LIKE '1999%'
AND GENDER = 'F'
LIMIT 20;

5.5 Where clause with EXISTS example

Exists is used with a subquery. IF the subquery returns any result(s) then the records from the table in the main query are returned.

SELECT * FROM EMPLOYEES WHERE 
EXISTS 
(SELECT 1 
FROM TITLES2);

Here the Titles2 table has no records and hence the subquery does not return any records. Due to this no records will be returned from the Employees table either.

SELECT * FROM EMPLOYEES WHERE 
EXISTS 
(SELECT 1 
FROM TITLES)
LIMIT 20;

Here the Titles table has records and hence employees table also returns records.

5.6 Not Exists Clause

Not Exists works exactly opposite to the Exists Clause as is seen in the below examples

SELECT * FROM EMPLOYEES WHERE 
NOT EXISTS 
(SELECT 1 
FROM TITLES2)
LIMIT 20;

Here, since the titles2 does not return any records and the “Not Exists” is used, the records from Employees table are returned.

SELECT * FROM EMPLOYEES WHERE 
NOT EXISTS 
(SELECT 1 
FROM TITLES);

Conversely, the above query will not return any records.

5.7 Where with IS NULL

Whenever we use the “Is NULL” for a column, then only the records with Null values for the specified column are returned. This is extremely useful while cleaning up data.

SELECT * FROM DEPT_EMP WHERE to_DATE IS NULL;

5.8 Where for multiple tables

Where clause is used for filtering records from multiple tables. These queries include joins, subqueries, operators, etc, and can be super complex. These are the queries in which optimization comes in handy. Given, below is a fairly simple example of getting data from multiple tables.

SELECT E.EMP_NO,E.FIRST_NAME,E.LAST_NAME,E.GENDER,D.DEPT_NAME,T.TITLE,S.SALARY
FROM EMPLOYEES E, DEPT_EMP DM,DEPARTMENTS D,SALARIES S,TITLES T
WHERE E.EMP_NO = DM.EMP_NO
AND DM.DEPT_NO = D.DEPT_NO
AND E.EMP_NO = S.EMP_NO
AND E.EMP_NO = T.EMP_NO
AND DM.TO_DATE = '9999-01-01'
AND S.TO_DATE = '9999-01-01'
AND T.TITLE='ENGINEER'
LIMIT 15;

6. Summary

In the article, we saw examples of using the Where Clause in MySQL. We also saw how it is optimized and its importance in query building. The Where Clause is an extremely important clause and is used with all the Data Manipulation Language constructs like Select, Update, Delete, etc.

7. Download the Source Code

This was an example of the SQL Where Clause using MySQL RDBMS.

Last updated on Nov. 07th, 2021

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.