SQL - WHERE Clause

---

The SQL WHERE clause is used to filter records while retrieving data from a single table or multiple tables (after join operation). This specifies a condition while fetching the data and if this condition is satisfied, then it returns filtered data from the table.

For instance, you can use the WHERE clause to retrieve details of employees of a department in an organization, or employees earning salary above/below certain amount, or details of students eligible for scholarships etc. This clause basically provides the specification of what type of data to be retrieved and the type of data to be neglected.

The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which we will learn about in the subsequent chapters.

Syntax

The basic syntax of the WHERE clause is as shown below.

Statement_Reference column1, column2,... columnN 
FROM table_name
WHERE [condition]

Here, the Statement_Reference can be any statement, such as SELECT, UPDATE, DELETE etc.

You can specify a condition using the comparison or logical operators like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear.

WHERE Clause in SELECT statement

We can use the WHERE clause in a SELECT statement to retrieve filtered rows based on a specific condition or expression. Following is the syntax for it −

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example

Assume we have created a table with name CUSTOMERS in SQL database using CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Following query inserts values into this table using the INSERT statement −

insert INTO CUSTOMERS VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

If we verify the contents of the CUSTOMERS table using the SELECT statement, we can observe the inserted records as shown below −

SELECT * from CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following query is an example which would fetch the ID, Name and Salary fields from the CUSTOMERS table created above using the SELECT statement, where the salary is greater than 2000 −

SELECT ID, NAME, SALARY 
FROM CUSTOMERS
WHERE SALARY > 2000;

Output

This would produce the following result −

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

WHERE Clause in UPDATE statement

We can also use the WHERE clause in a UPDATE statement to update rows in a table which satisfy a certain condition. If the WHERE clause is not used, the UPDATE statement would affect all the records of a table. Following is the syntax −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

In the following query we are trying to update the age of all the customers whose age is ‘25’ and updating it to ‘30’ using UPDATE statement −

UPDATE CUSTOMERS
set AGE = 30
where AGE = 25;

Output

We get the following result. We can observe that the age of 2 customers have been modified −

(2 rows affected)

Verification

To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables as shown in the following query −

SELECT * from CUSTOMERS;

The table is displayed as follows −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  30 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  30 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

As we can see in the above table, the AGE of ‘Khilan’ and ‘Chaitali’ has been updated to ‘30’.

WHERE Clause with IN operator

The WHERE clause, when with IN operator, compares the records of a table with a certain list of values. If this clause is used with the SELECT statement, it allows us to retrieve the rows in a table that match any of the values in the specified list. Following is the syntax for it −

WHERE column_name IN (value1, value2, ...);

Where, the column_name is the column of a table and value1, value2, etc. are the list of values that we want to compare with the column_name.

Example

Suppose you want to display records with NAME equalling to ‘Khilan’, ‘Hardik’ and ‘Muffy’ (string values) from the CUSTOMERS table, you can use the following query IN operator −

SELECT * from CUSTOMERS
WHERE NAME IN ('Khilan', 'Hardik', 'Muffy');

Output

The result obtained is as follows −

+----+--------+-----+---------+----------+
| ID | NAME   | AGE | ADDRESS | SALARY   |
+----+--------+-----+---------+----------+
|  2 | Khilan |  25 | Delhi   |  1500.00 |
|  5 | Hardik |  27 | Bhopal  |  8500.00 |
|  7 | Muffy  |  24 | Indore  | 10000.00 |
+----+--------+-----+---------+----------+

WHERE Clause with NOT IN operator

Using the WHERE clause with NOT IN operator will perform the negation of WHERE clause with IN operation. The IN operator will compare the rows of table with the list of values and the NOT operator will except the rows that are matched. Hence, when used with SELECT statement, the rows that do not match the list of values are retrieved. Following is the syntax −

WHERE column_name NOT IN (value1, value2, ...);

Example

In this example, we are trying to display the records from CUSTOMERS table, where AGE is NOT equal to ‘25’, ‘23’ and ‘22’ as shown in the query below −

SELECT * from CUSTOMERS
WHERE AGE NOT IN (25, 23, 22);

Output

We obtain the result as given below −

+----+--------+-----+-----------+----------+
| ID | NAME   | AGE | ADDRESS   | SALARY   |
+----+--------+-----+-----------+----------+
|  1 | Ramesh |  32 | Ahmedabad |  2000.00 |
|  5 | Hardik |  27 | Bhopal    |  8500.00 |
|  7 | Muffy  |  24 | Indore    | 10000.00 |
+----+--------+-----+-----------+----------+

WHERE Clause with LIKE operator

The WHERE clause with LIKE operator allows us to filter rows that matches a specific pattern. This specific pattern is represented by wildcards (such as %, _, [] etc). Following is the syntax −

WHERE column_name LIKE pattern;

Where, column_name is the column that we want to compare the pattern against and pattern is a string that can contain wildcards (such as %, _, [] etc).

Example

Following is the query, which would display all the records where the name starts with K and is at least 4 characters in length −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE 'K___%';

Output

The result obtained is given below −

+----+---------+-----+---------+---------+
| ID | NAME    | AGE | ADDRESS | SALARY  | 
+----+---------+-----+---------+---------+
|  2 | Khilan  |  25 | Delhi   | 1500.00 | 
|  3 | kaushik |  23 | Kota    | 2000.00 | 
|  6 | Komal   |  22 | MP      | 4500.00 | 
+----+---------+-----+---------+---------+

WHERE Clause with AND, OR operators

We can use AND and OR operators together in SQL to combine multiple conditions in a WHERE clause to filter rows that meets the specified criteria. AND operator will make sure only those rows are filtered that satisfy all the conditions and OR operator will filtered records that satisfy any one condition specified. However, this is only used when specifying one condition is not enough to filter all the required rows.

Following is the syntax for using the AND and OR operators in a WHERE clause −

WHERE (condition1 OR condition2) AND condition3;

Example

In the following query, we are trying to retrieve all rows from the "CUSTOMERS" table based on some conditions. The parentheses control the order of evaluation so that the OR operator is applied first, followed by the AND operator −

SELECT * FROM CUSTOMERS
WHERE (AGE = 25 OR salary < 4500) AND (name = 'Komal' OR name = 'Kaushik');

Output

This would produce the following result −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+