SQL WHERE command in MySQL table for conditional retrieval of records

SELECT * FROM table_name WHERE condition

WHERE clause is used to collect the records from the table based on some condition specified. More than one condition can be added to the where clause by using various logical expressions like AND, OR, < ( less than ), greater than (> ) etc. Logical expressions plays important role in returning the desire records. Let us start with some examples.

We are interested in the marks of Fourth class students. You can just follow the table creation process and the select query section discussed here. We will use the same table to work with where clause.
Here is the table with few rows.

id	name	class	mark	sex
1	John Deo	Four	75	female
2	Max Ruin	Three	90	male
3	Arnold	Three	55	male
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
6	Alex John	Four	55	male

To get only Class Four records

SELECT * FROM student WHERE  class='Four'

Here is the result

id	name	class	mark	sex
1	John Deo	Four	75	female
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
6	Alex John	Four	55	male
10	Big John	Four	55	female
15	Tade Row	Four	93	male
16	Gimmy	Four	93	male
21	Babby John	Four	69	female
31	Marry Toeey	Four	93	male

This will return all the records from the table name=student of class=Four. This is what we require to get all the records of fourth standard students.

WHERE with AND

Now let us add little more requirement to this and go for all the records of students of fourth standard who have scored mark more than 70.

SELECT * FROM student WHERE  class='Four' AND  mark >70

id	name	class	mark	sex
1	John Deo	Four	75	female
15	Tade Row	Four	93	male
16	Gimmy	Four	93	male
31	Marry Toeey	Four	93	male

We have added one more condition in where clause with a AND combination. This query will return all student records of Fourth class who have scored more than 70. There are different logical combinations using which can work on different type of fields depending on the required conditions. We can see all the non numeric fields we have to use quotes and for numeric fields we need to use quotes. Now let us try some more commands and see what result we will get.

WHERE with BETWEEN

SELECT * FROM `student` WHERE mark BETWEEN 60 and 70

id	name	class	mark	sex
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
20	Jackly	Nine	65	female
21	Babby John	Four	69	female
34	Gain Toe	Seven	69	male

This will return all the records of the student table that have scored marks between 60 and 70.

Using LIKE with WHERE

SELECT * FROM `student` WHERE name LIKE '%John%'

id	name	class	mark	sex
1	John Deo	Four	75	female
5	John Mike	Four	60	female
6	Alex John	Four	55	male
7	My John Rob	Fifth	78	male
10	Big John	Four	55	female
21	Babby John	Four	69	female

This search will return us all the records for which inside the name field 'John' is used. John name can be a part of the field. This will return records having name = John Deo or John Mike and even names like Alex John.

SQL WHERE Command

WHERE with AND

WHERE with BETWEEN

Using LIKE with WHERE

SQL Popular Tutorials