When we have to UPDATE, DELETE or SELECT a particular set of data we use SQL WHERE clause. The WHERE clause is an integral part of SQL as it provides the feature to add conditions to a query.
SQL Where Clause
SQL where clause is one of the most widely used SQL keyword because it’s part of most of the queries. Let’s see the general syntax of where clause in sql query.
|
1 2 3 |
SELECT column(s) FROM table_name WHERE condition; |
In the syntax above the column data is retrieved based on the condition that is specified as part of WHERE condition.
Similarly we can use it with Update and Delete query as follows:
|
1 2 3 4 |
update Employee set name="David" where id=1; Delete from Employee where name="John"; |
SQL Query Where Example
Let’s try to understand the SQL WHERE command through some example. We have a sample table with below structure and data.
| CustomerId | CustomerName | CustomerAge | CustomerGender |
|---|---|---|---|
| 1 | John | 31 | M |
| 2 | Amit | 25 | M |
| 3 | Annie | 35 | F |
Now we want to select all the customer names who are Female. Below is the SQL query with where clause to get this data.
|
1 2 3 |
SELECT CutomerName FROM Customer WHERE CustomerGender="F"; |
SQL Where Clause Operators
Since where clause is to add conditions to the query, there are many operators that we can use with it. Let’s look into commonly used operators with sql query where clause.
| Operator | Description | Example |
|---|---|---|
| = | Equal to | SELECT CustomerNameFROM Customer WHERE CustomerGender = ‘F’; |
| != , <> | Not equal to | SELECT CustomerName FROM Customer WHERECustomerGender != ‘M’; |
| > | Greater than | SELECT CustomerNameFROM Customer WHERE CustomerAge > 32; |
| < | Less than | SELECT CustomerName FROM Customer WHERECustomerAge < 30; |
| >= | Greater than or equalto | SELECT CustomerNameFROM Customer WHERE CustomerAge >= 35; |
| <= | Less than or equal to | SELECT CustomerName FROM Customer WHERECustomerAge <= 30; |
| IN | Value is from thespecified list of values | SELECT CustomerNameFROM Customer WHERE CustomerGender IN (‘M’, ‘F’); |
| BETWEEN | Between the specified range | SELECT CustomerName FROM Customer WHERECustomerAge BETWEEN (25,30); |
| LIKE | Search for pattern | SELECT CustomerNameFROM Customer WHERE CustomerName like ‘Ann%’; |
Numeric and text are used as part of the WHERE clause values in different ways. To use Numeric no quotes should be used. For using a text value single quotes should be used.
That’s all for SQL where clause. Where clause in SQL query is used a lot, so you should understand it properly.