Where clause is used to fetch a particular row or set of rows from a table. This clause filters records based on given conditions and only those row(s) comes out as result that satisfies the condition defined in WHERE clause of the SQL query.
SQL Where Clause Syntax
SELECT Column_name1, Column_name2, .... FROM Table_name WHERE Condition;
Here we have used the where clause with the SQL SELECT statement, however we can use this clause with other SQL statements as well such as UPDATE, DELETE etc.
Example – SQL WHERE Clause
We have a table EMPLOYEES:
+------+----------+---------+----------+ |SSN | EMP_NAME | EMP_AGE |EMP_SALARY| +------+----------+---------+----------+ | 101 | Steve | 23 | 9000.00 | | 223 | Peter | 24 | 2550.00 | | 388 | Shubham | 19 | 2444.00 | | 499 | Chaitanya| 29 | 6588.00 | | 589 | Apoorv | 21 | 1400.00 | | 689 | Rajat | 24 | 8900.00 | | 700 | Ajeet | 20 | 18300.00 | +------+----------+---------+----------+
Lets say we want to fetch the name of the those employees who are more than 23 years old. The SQL statement would look like this –
Query:
SELECT EMP_NAME FROM EMPLOYEES WHERE EMP_AGE > 23;
Result:
+----------+ | EMP_NAME | +----------+ | Peter | | Chaitanya| | Rajat | +----------+
Fetch all the details of employees having salary greater than 6000.
Query:
SELECT * FROM EMPLOYEES WHERE EMP_SALARY > 6000;
Result:
+------+----------+---------+----------+ |SSN | EMP_NAME | EMP_AGE |EMP_SALARY| +------+----------+---------+----------+ | 101 | Steve | 23 | 9000.00 | | 499 | Chaitanya| 29 | 6588.00 | | 689 | Rajat | 24 | 8900.00 | | 700 | Ajeet | 20 | 18300.00 | +------+----------+---------+----------+
Text Field in Where clause condition
In the above examples, we have seen that when the condition required numeric values in conditions such as employee salary & employee age, we didn’t require the quotes. However when the condition involves a column that has text value then in such case we have to enclose the value in single quotes. Lets take an example to understand this.
Lets say I want to find out the SSN of employee “Ajeet”. I can do it like this:
Query:
SELECT SSN, EMP_NAME FROM EMPLOYEES WHERE EMP_NAME = 'Ajeet';
Note: Do note the single quotes around String “Ajeet”.
Result:
+------+----------+ |SSN | EMP_NAME | +------+----------+ | 700 | Ajeet | +------+----------+
Operators allowed in The WHERE Clause conditions
In the above examples we have seen that where clause allowed operators as > & =. Lets see the complete list of operators that we can use in the where clause.
Operators List:
> Greater than operator
< Less than operator
= Equal operator
>= Greater than or equal
<= Less than or equal
<> Not equal.
IN To specify the set of values
BETWEEN To specify the range of values
LIKE To specify the pattern
SQL where clause with multiple conditions
Lets take the same table:
+------+----------+---------+----------+ |SSN | EMP_NAME | EMP_AGE |EMP_SALARY| +------+----------+---------+----------+ | 101 | Steve | 23 | 9000.00 | | 223 | Peter | 24 | 2550.00 | | 388 | Shubham | 19 | 2444.00 | | 499 | Chaitanya| 29 | 6588.00 | | 589 | Apoorv | 21 | 1400.00 | | 689 | Rajat | 24 | 8900.00 | | 700 | Ajeet | 20 | 18300.00 | +------+----------+---------+----------+
Lets fetch the employee details where employee age is greater than 23 and salary is greater than 5000. For such query we have to use multiple conditions in where clause.
Query:
SELECT * FROM EMPLOYEES WHERE EMP_SALARY > 5000 AND EMP_AGE > 23;
Result:
+------+----------+---------+----------+ |SSN | EMP_NAME | EMP_AGE |EMP_SALARY| +------+----------+---------+----------+ | 499 | Chaitanya| 29 | 6588.00 | | 689 | Rajat | 24 | 8900.00 | +------+----------+---------+----------+
Another multiple conditions example: Fetch the employee names, where either employee age is less than 20 or salary is less than 5000.
Query:
SELECT EMP_NAME FROM EMPLOYEES WHERE EMP_SALARY < 5000 OR EMP_AGE < 20;
Result:
+----------+ | EMP_NAME | +----------+ | Shubham | | Peter | | Apoorv | +----------+