Use of WHERE Clause

In relational database system,WHERE clause is used to filter the records or data. In other way you can say that, WHERE clause fetch those records only which full fill the specific condition.

WHERE clause Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Here

• SELECT column1 , column2, ... : is standard SELECT statement which is used to select the required number of columns. It may be up to N number of columns depends on number of table's columns.
• FROM table_name  : This is used for selecting the required table from where you want to fetch the records. It may be more then one table in number..like SELECT table1 , table2 , table2 ....
• WHERE conditions : Used here for filtering the records. As per applied condition, records shall be fetched.  

Note*:-WHERE clause is used in SELECT statements, apart from this you can also used it in UPDATE DELETE statements also.

Now theory is enough, lets go with practical examples 😌

Note*:- For all examples we will use MySQL database and MySql workbench as an editor client.

Assumed that ,  have a student table with following fields.

and I have already fed the data in this table.

Use cases

Case 1: Simple WHERE condition in queries : - If you want to fetch the record of students whose id is 103, then we can do this like.

SELECT *FROM student WHERE sid =103;

here '*' selects all columns of the table student. We can also write the same query like this

SELECT sid,sname,smobile,saddress FROM student WHERE sid =103;

Case 2: WHERE with logical AND : - If we apply more then one condition simultaneously and both must be satisfied , then we can go for logical AND. Lets say, if we want to fetch the record of students whose id is 103 and address must be 'South-x' then query goes like this...

SELECT *
FROM student
WHERE sid = 103 AND saddress = 'South-x';

Case 3: WHERE with logical OR : - If even single condition is true, result will produce in case of logical OR.

SELECT *
FROM student
WHERE sid = 101 OR saddress = 'South-x';

Note*:-In case of AND both conditions must be true but in OR conditions even one condition is true,it will produce result.

Case 4: WHERE with IN(...) : - If we want to supply multiple values in WHERE clause then we can use IN() operator. Lets see in the query....

SELECT *
FROM student
WHERE sid IN(101,102,103);

Case 5: WHERE with NOT IN(...) : - If we want to exclude multiple values from fetched records, then we can use NOT IN() operator. Lets see in the query....

SELECT *
FROM student
WHERE sid NOT IN(101,102,103);

Case 6: WHERE with COMPARISON OPERATOR: - WHERE is also used with comparison operators

• =   Equal to :- Record fetched if exact match. 
• >   Greater than :-All records fetched greater then supplied value.
• <   Less than :- All records fetched less then supplied value.
• <> Not Equal to :- All records fetched except supplied value.

SELECT * FROM student WHERE sid = 100;
SELECT * FROM student WHERE sid > 100;
SELECT * FROM student WHERE sid < 105
SELECT * FROM student WHERE sid <> 100;

Case 7: WHERE with DELETE: - WHERE is also used for deleting the specific records. Here we are going to delete the record of student whose id is 100.

DELETE 
FROM student
WHERE sid = 100;

Case 8: WHERE with UPDATE : - WHERE is also used with updating the specific records. Here we are going to update the mobile number whose id is 103.

UPDATE student SET smobile = '9887876567'
WHERE sid = 103;

It will update mobile no of student whose id is 103.

Note*:-During updating or deleting the records  from the table, do it carefully, if we do it without WHERE clause, either wipe out or modified whole data.

Summary

• The SQL WHERE clause is used to restrict the number of rows affected by a SELECT, UPDATE or DELETE query.
• The WHERE clause can be used in conjunction with logical operators such as AND and OR, comparison operators such as ,= etc.
• When used with the AND logical operator, all the criteria must be met.
• When used with the OR logical operator, any of the criteria must be met.
• The key word IN is used to select rows matching a list of values.