Using the WHERE SQL clause

SQL WHERE clause is used to specify/apply any condition while retrieving, updating, or deleting data from a table. A WHERE clause in SQL is a data manipulation language (DML) statement. This clause is used mostly with SELECT, UPDATE and DELETE query. When we specify a condition using the WHERE clause, then the query executes only for those records for which the condition specified by the WHERE clause is true. Actually. it filters the records. The WHERE clause is used at the end of any SQL query, to specify a condition for execution.

Syntax:

    SELECT/UPDATE/DELETE FROM table_name 
    WHERE [condition];

Let us consider a table employee with the following five fields and four records.

Example: WHERE clause with SELECT statement

In this example, will use the SELECT statement to display data of the table, based on a condition, which we will add to our SELECT query using the WHERE clause. The below SQL query displays the record for the employee with id as 101.

    SELECT * FROM employee 
    WHERE id = 101;

Output:

Example: WHERE clause with DELETE statement

In this example, we delete a particular record based on the WHERE condition by using the DELETE command. The below SQL query deletes a record where the name is Richard.

    DELETE FROM employee
    WHERE name = 'Richard';

    #displays records after deleting
    SELECT * FROM employee;

Output:

Example: WHERE clause with UPDATE statement

In this example, we update a particular record based on the WHERE condition using the UPDATE command. The below SQL query updates the salary by 1000 where name = Adam. Here, I have used + operator to add 1000 to the salary. In this way, you can use any of the conditional or logical operators.

    UPDATE employee
    SET salary = salary+1000
    WHERE name = 'Adam';

    #displays records after updating
    SELECT * FROM employee;

Output:

Operators for WHERE Clause Conditions

WHERE clause uses some conditional, logical selection. The below table illustrates the list of operators that can be used while specifying the WHERE clause condition.

Operators	Description
=	Equal to
!=	Not Equal to
<	Less Than
>	Greater Than
<=	Less Than or Equal to
>=	Greater Than or Equal to
BETWEEN	Between a specified range of values
LIKE	It is used to search for a pattern in a string
IN	In a given set of values

Conclusion

In this article, we discussed the WHERE clause and its various use cases. We also learned different operators that can be used with the WHERE clause.

---