WHERE Clause | Database

Introduction

WHERE clause is used to specify condition while retrieving, updating or deleting data from a table. When we specify a condition using the WHERE clause then the query executes only for those records for which the condition specified by the WHERE clause is true.

Syntax

WHERE clause is used at the end of any SQL query, to specify a condition for execution.

/* WHERE clause with a SELECT statement*/
SELECT
  column_list
FROM
  table_name
WHERE
  condition;


/* WHERE clause with a DELETE statement*/
DELETE 
FROM 
  table_name 
WHERE 
  condition;

WHERE clause syntax with AND, NOT and OR

/* WHERE clause with AND */
SELECT
  column_names
FROM
  table_name
WHERE
  condition1 AND condition2

/* WHERE clause with OR */
UPDATE
  table_name
SET
  column_name = value
WHERE
  condition1 OR condition2

/* WHERE clause with NOT */
DELETE
  table_name
WHERE NOT
  condition

Example

Consider below table

The following statement retrieves all employee based on their city.

SELECT * FROM employleinfo WHERE NOT city = 'Patna';

/* Query Result */
id  firstName lastName  age city
1   Rahul     Bhagwat   20  Noida
2   Stephen   Fleming   17  Delhi
4   New Name  Sharma    50  Mumbai
5   Erica     Edwards   40  Chennai
6   Priya     Gowda     50  Pune
7   Shekar    Chandra   43  Delhi
9   Garima    Singh     30  Lucknow


SELECT * FROM employleinfo WHERE city = 'Patna';

/* Query Result */
id  firstName lastName  age city
3   Sebastian Smith     19  Patna
8   Rahul     Singh     19  Patna

Below example filter the result set using AND and OR.

SELECT * FROM employleinfo WHERE city = 'Delhi' AND id=7;

/* Query Result */
id  firstName lastName  age city
7   Shekar    Chandra   43  Delhi


SELECT * FROM employleinfo WHERE id < 2 OR id > 9;

/* Query Result */
id  firstName lastName  age city
1   Rahul     Bhagwat   20  Noida

Filter the result in range of values using BETWEEN and IN

SELECT * FROM employleinfo WHERE id BETWEEN 1 and 5;

/* Query Result */
id  firstName lastName  age city
1   Rahul     Bhagwat   20  Noida
2   Stephen   Fleming   17  Delhi
3   Sebastian Smith     19  Patna
4   New Name  Sharma    50  Mumbai
5   Erica     Edwards   40  Chennai


SELECT * FROM employleinfo WHERE city IN ('Mumbai', 'Pune');

/* Query Result */
id  firstName lastName  age city
4   New Name  Sharma    50  Mumbai
6   Priya     Gowda     50  Pune

This example filter the result using wildcard pattern.

SELECT * FROM employleinfo WHERE firstName LIKE '%ri%';

/* Query Result */
id  firstName lastName  age city
5   Erica     Edwards   40  Chennai
6   Priya     Gowda     50  Pune
9   Garima    Singh     30  Lucknow

Reference

Using the WHERE SQL clause