;

SQL WHERE


In this tutorial, we will learn how to select a record based on a specific condition from the table using  SQL.

SQL WHERE Clause

SQL WHERE clause is used to filter records. WHERE clause is used to specify a condition while fetching/retrieving the records from a single table or by joining with multiple tables. If given condition satisfied, only then it returns a specific record or value from the table.

Syntax - WHERE Clause With SELECT Statement 
SELECT column1, column2....columnN 
FROM tableName
WHERE [CONDITION];
Syntax - WHERE Clause With UPDATE Statement
UPDATE tableName
SET column1=[new value1],column2=[new value2]....columnN=[new valueN]
WHERE [CONDITION];
Syntax - WHERE Clause With DELETE Statement
DELETE FROM tableName
WHERE [CONDITION];

Example:

Let us consider this table "Employee" for records.

Table Name: Employee

ID EmpName City Country Gender Salary
1 Shankar Delhi India male 25000
2 Sourabh Delhi India male 30000
3 Ranvijay Mumbai India male 15000
4 Kapil Noida India male 25000
5 Shalini Jaipur India female 18000
6 Rakesh Faridabad India male 23000
7 Akshay Mumbai India male 21000
8 Sarah New York US female 76000
9 Rocky Noida India male 28000

Here are some examples of Where clause:

For finding an employee whose salary is greater than 25000,

Example - WHERE Clause
SELECT * FROM Employee
WHERE Salary > 25000;
Output
ID EmpName City Country Gender Salary
2 Sourabh Delhi India male 30000
8 Sarah New York US female 76000
9 Rocky Noida India male 28000

Multiple Conditions

You can Filter Records based on multiple Condition using operators: AND and OR Operator

AND Operator: The AND operator filters the records only when both the first condition and second condition is satisfy.

For finding an employee whose City is "Delhi" and Country is "India"

Example - WHERE Clause With AND Operator
SELECT * FROM Employee
WHERE City='Delhi' AND Country='India';
Output - WHERE Clause With AND Operator
ID EmpName City Country Gender Salary
1 Shankar Delhi India male 25000
2 Sourabh Delhi India male 30000

OR Operator: The OR operator filters the records when either the first condition or second condition is satisfied.

For finding an employee whose City is "Delhi" or Country is "India"

Example - WHERE Clause With OR Operator
SELECT * FROM Employee
WHERE City='Delhi' OR Country='India';
Output - WHERE Clause With OR Operator
ID EmpName City Country Gender Salary
1 Shankar Delhi India male 25000
2 Sourabh Delhi India male 30000
3 Ranvijay Mumbai India male 15000
4 Kapil Noida India male 25000
5 Shalini Jaipur India female 18000
6 Rakesh Faridabad India male 23000
7 Akshay Mumbai India male 21000
9 Rocky Noida India male 28000

Operators used with WHERE Clause

The following operators can be used in the WHERE clause:

Operator Description
= Equal
<> Not equal.  Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column