MySQL Where Clause


MySQL Where Clause is used to filter records. The where clause is used to extract only those records which are fulfilling its conditions.
Where clause can not be used as a standalone statement, it is used with SELECT, UPDATE, INSET, Delete commands. The general syntax of where clause with SELECT statement is as follows

select columnName,ColumnName...........
from tableName
where condition;

A demo table Student having the following records will be used for demonstration purpose

mysql> select * from student;
+-------+-------+----------+----------+------+------------+---------+-------+--------+
| index | admno | name     | fname    | std  | stream     | fees    | grade | gender |
+-------+-------+----------+----------+------+------------+---------+-------+--------+
|     0 |     1 | ramji    | dashrath | 12   | science    | 2600.45 | A     | m      |
|     1 |     2 | shlok    | rath     | 11   | commerce   | 2300.45 | B     | m      |
|     2 |     3 | zarleesh | abdul    | 12   | humanities |    2100 | A2    | F      |
|     3 |     4 | nipun    | suresh   | 11   | science    | 2400.25 | A1    | m      |
|     4 |     5 | shlok    | 999      | 999  | 999        |     999 | 999   | 999    |
|     5 |     1 | ramji    | dashrath | 12   | science    | 2500.45 | A     | m      |
|     0 |     1 | ramji    | dashrath | 12   | science    | 2600.45 | A     | m      |
|     1 |     2 | shlok    | rath     | 11   | commerce   | 2300.45 | B     | m      |
|     2 |     3 | zarleesh | abdul    | 12   | humanities |    2100 | A2    | F      |
|     3 |     4 | nipun    | suresh   | 11   | science    | 2400.25 | A1    | m      |
|     4 |     5 | shlok    | 999      | 999  | 999        |     999 | 999   | 999    |
|     5 |     1 | ramji    | dashrath | 12   | science    | 2500.45 | A     | m      |
+-------+-------+----------+----------+------+------------+---------+-------+--------+
12 rows in set (0.02 sec)

Where clause Examples

Try the following commands on mysql> Prompt

root@host# mysql -u root -p password;
Enter password:*******
mysql> use SCHOOL;
Database changed
mysql> select * from student where admno=5;
+-------+-------+-------+-------+------+--------+------+-------+--------+
| index | admno | name  | fname | std  | stream | fees | grade | gender |
+-------+-------+-------+-------+------+--------+------+-------+--------+
|     4 |     5 | shlok | 999   | 999  | 999    |  999 | 999   | 999    |
|     4 |     5 | shlok | 999   | 999  | 999    |  999 | 999   | 999    |
+-------+-------+-------+-------+------+--------+------+-------+--------+
2 rows in set (0.00 sec)


mysql> select admno,name,fname from student where stream='science' and std=12;
+-------+-------+----------+
| admno | name  | fname    |
+-------+-------+----------+
|     1 | ramji | dashrath |
|     1 | ramji | dashrath |
|     1 | ramji | dashrath |
|     1 | ramji | dashrath |
+-------+-------+----------+
4 rows in set (0.00 sec)

NOTE:

  1. Text field need inverted quotation marks when compared whereas numeric field does not require quotation marks
  2. Different types of relational operators as well as logical operators can be used to generate our condition.

Operators  in Where Clause (Relational and Logical )

OperatorExplanation
=Equal to
>Greater than
<Less than
>=Greater than Equal to
<=Less than Equal to
<>Not Equal to. IN some version of SQL != is also used to the same
BETWEENBetween a certain Range
LIKEUsed for pattern matching
INUsed to check values in a Domain of value
ANDCombine Two conditions, Return True if both conditions result in True
ORReturn True if any one condition results in True
NOTReturn True if the result is false and vice versa
Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.