The WHERE clause is used to filter data from multiple records. It is used to extract only those records that are specified against some given conditions.
Where clause is used to fetch a particular row data or set of rows from a selected table. This clause filters records depending on given conditions and only those row(s) come out as a result that satisfies the condition defined in the WHERE clause of the SQL query.
The SQL WHERE clause is used to filter the results and apply conditions like INSERT, UPDATE, DELETE, or SELECT statement.
In another word, the WHERE clause is used to filter records that are used to extract only those records that fulfill a given condition.
SQL WHERE Clause Syntax
SELECT <TableColumn1>, <TableColumn2>, ....FROM <YourTableName> WHERE Condition;
Here you have used the WHERE clause with the SQL SELECT statement, however, you can use this clause with other SQL statements as well such as SELECT, UPDATE, DELETE, etc.
SQL WHERE Clause Example
| FirstName | LastName | Address | Age |
| Sylvia | Neupane | Kathmandu | 10 |
| Rahul | Bhattarai | Pokhara | 22 |
| Ubina | Thapa | Mirmee | 23 |
| Saurab | Rai | Pokhara | 10 |
| Sylvia | Gurung | Butwal | 21 |
Let's say I want to fetch the FirstName of that Employee who is more than 15 years old. The SQL statement would look like this given below
SELECT FirstName FROM EmployeeInfo WHERE Age > 15;
| FirstName |
| Rahul |
| Ubina |
| Sylvia |
The following query is an example, which would fetch the FirstName, LastName, and Address fields from the EmployeeInfo table for an Employee with the Address Pokhara
Here, it is important to note that all the strings should be given inside single quotes (''). Whereas, numeric values should be given without any quote as in the above example.
SELECT FirstName, LastName, Address FROM EmployeeInfo WHERE Address= 'Pokhara';
This would produce the following result
| FirstName | LastName | Address |
| Rahul | Bhattarai | Pokhara |
| Saurab | Rai | Pokhara |
Operators allowed in The WHERE Clause conditions
In the above examples, you have seen that the WHERE clause allowed operators as > & =. Let's see the complete list of operators that you can use in the where clause.
Operators List:
- > Greater than the operator
- < Less than the 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:
| FirstName | LastName | Address | Age |
| Sylvia | Neupane | Kathmandu | 10 |
| Rahul | Bhattarai | Pokhara | 22 |
| Ubina | Thapa | Mirmee | 23 |
| Saurab | Rai | Pokhara | 10 |
| Sylvia | Gurung | Butwal | 21 |
Let's fetch the Employee details where Employee Age is greater than 15 and Address is Pokhara. For such a query, we have to use multiple conditions in the WHERE clause.
SELECT * FROM EmployeeInfo WHERE Age >15 AND Address ='Pokhara'
Result
| FirstName | LastName | Address | Age |
| Rahul | Bhattarai | Pokhara | 22 |
Text Fields vs. Numeric Fields
The SQL requires single quotes around text values (most database systems will also allow double quotes).
However, numeric fields should not be enclosed in quotes:
Example
SELECT * FROM EmployeeInfo WHERE Age =10;Output
| FirstName | LastName | Address | Age |
| Sylvia | Neupane | Kathmandu | 10 |