sql

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,

  • σ= selector
  • cond = filter condition i.e. where clause in a SQL query.
  • Relation Name = name of the table.

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.

SQL Where - Sample explain plan in MySQL workbench
Sample explain plan in MySQL workbench

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';
SQL Where - Simple Queries with =
Simple Queries with = in where clause

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;
SQL Where - Using  greater than , less than  and not equals operators
Using greater than , less than and not equals operators

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;
Using In and Not IN
Using In and Not IN

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

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;
And, Or and Like
And,Or,LIKE

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.

Exists Clause in Where
Exists Clause in Where

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.

Not Exists with sql Where
Not Exists with Where

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;
IS NULL in sql Where
IS NULL in Where

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;
Joins using sql Where
Joins

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.

Download
You can download the full source code of this example here: SQL Where Clause Example

Last updated on Nov. 07th, 2021

Reshma Sathe

I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button