The SQL Where Clause

The SQL WHERE clause is used to filter the results obtained by the DML statements such as SELECT, UPDATE and DELETE etc. We can retrieve the data from a single table or multiple tables(after join operation) using the WHERE clause.

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 which records to be retrieved and which are to be to be neglected.

Syntax

The basic syntax of the SQL WHERE clause is as shown below −

DML_Statement column1, column2,... columnN
FROM table_name
WHERE [condition];

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

You can specify a condition using the such as, >, <, =, LIKE, NOT, etc.

WHERE Clause with SELECT Statement

Typically, the SELECT statement is used to retrieve data from a table. If we use the WHERE clause with the SELECT statement, we can filter the rows to be retrieved 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 named CUSTOMERS in MySQL 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 INSERT query inserts 7 records into this table −

INSERT INTO CUSTOMERS VALUES
(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, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table created is as shown below −

In the following query, we are fetching the ID, NAME and SALARY fields from the CUSTOMERS table for the records where the SALARY is greater than 2000 −

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

Output

This would produce the following result −

WHERE Clause with UPDATE Statement

The UPDATE statement is used to modify the existing records in a table. Using the SQL WHERE clause with the UPDATE statement, we can update particular records. 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 incrementing the salary of the customer named Ramesh by 10000 by using the WHERE clause along with the UPDATE statement −

UPDATE CUSTOMERS set SALARY = SALARY+10000 
where NAME = ''Ramesh

Output

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

Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

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

SELECT * FROM CUSTOMERS WHERE NAME = ''Ramesh

The table is displayed as follows −

WHERE Clause with IN Operator

Using the IN operator you can specify the list of values or sub query in the where clause. If you use WHERE and IN 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 values Khilan, Hardik and Muffy from the CUSTOMERS table, you can use the following query −

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

Output

The result obtained is as follows −

WHERE Clause with NOT IN Operator

The WHERE clause with NOT IN operator is the negation of WHERE clause with the IN operator.

• If you use WHERE with the IN operator, the DML statement will act on the the list of values (of a column) specified
• Whereas, if you use WHERE with the NOT IN operator, the DML operation is performed on the values (of a column) that are not there in the specified list.

Hence, if you use WHERE Clause with NOT IN Operator along with the 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 displaying the records from CUSTOMERS table, where AGE is NOT equal to 25, 23 and 22.

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

Output

We obtain the result as given below −

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 −

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. The AND operator will make sure only those rows are filtered that satisfy all the conditions and the OR operator will filter records that satisfy any one of the specified conditions. 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 retrieving 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 −