SQL WHERE Multiple Conditions

In this topic, we will learn how to add multiple conditions using the WHERE clause.

First, let's understand the concept of WHERE clause.

WHERE clause is used to specify a condition while retrieving records from a table.

WHERE clause is generally used with SELECT statement in SQL

The SELECT query will display only the records satisfying the condition specified in the WHERE clause

There can be one or more than one condition specified in WHERE clause condition of a SELECT query.

The AND and OR operators are used to check multiple conditions using the WHERE clause in a single query.

• AND operator with WHERE clause

Whenever AND operator is used in between the conditions with WHERE clause to check multiple conditions in SELECT query, the results are displayed when only more than one condition given in the query is met.

Syntax:

SELECT *FROM table_name WHERE Condition 1 AND Condition 2 [AND Condition 3];

Example 1:

Consider the students table given below. We will consider the same table for all the following examples.

Student_RollNo	Student_Name	Student_Gender	Student_MobileNumber	Student_HomeTown	Student_Age	Student_Percentage
1	Rohit More	Male	9890786123	Lucknow	23	75
2	Kunal Shah	Male	7789056784	Chandigarh	20	68
3	Kartik Goenka	Male	9908743576	Ahemdabad	22	92
4	Anupama Shah	Female	8890907656	Chandigarh	24	94
5	Snehal Jain	Female	8657983476	Surat	21	94

Now, let us see few examples to understand this concept practically.

mysql> SELECT *FROM students WHERE Student_Gender = "Male" AND Student_HomeTown = "Chandigarh";

Output:

There is only one record in the students table with roll number 2, which has gender as male and home town as Chandigarh. Even if any one of the specified conditions is not met, then, in that case, the output will not be the same. The record/records displayed will be different, or in some cases, the output may be an empty set if no record satisfies both conditions.

Example 2:

mysql> SELECT *FROM students WHERE Student_Age = 21 AND Student_Percentage = 94;

Output:

There is only one record with roll number ‘5’ in the students table, which has age equals to ‘21’ and percentage equal to ‘94’.

Example 3:

mysql> SELECT *FROM students WHERE Student_Gender = "Female" AND Student_HomeTown = "Chandigarh" AND Student_Age = 24;

Output:

Among all the records in the students table, there is only one record with roll number ‘4’, which has gender as ‘male’, the home town as ‘Chandigarh’, and age equals to '24'. This is the only record that satisfies all three conditions.

Example 4:

mysql> SELECT *FROM students WHERE Student_Gender = "Male" AND  Student_Name = "Kartik Goenka" AND Student_Percentage = 92;

Output:

Among all the records in the students table, there is only one record with roll number ‘3’ in which all the three conditions are met, i.e., gender as male, name equals to 'Kartik Goenka' and percentage equals to '92'.

•  OR operator with WHERE clause

Whenever OR operator is used in between the conditions with WHERE clause to check multiple conditions in SELECT query, then the results are displayed when at least one condition is met among all the other conditions written in a query.

Syntax:

SELECT *FROM table_name WHERE Condition 1 OR Condition 2 [OR Condition 3];

Example 1:

mysql> SELECT *FROM students WHERE Student_Gender = "Male" OR Student_HomeTown = "Chandigarh";

Output:

In students table, there are four records with roll number 1, 2, 3 and 4 which has gender as male or home town as Chandigarh. Even if any specified conditions are met, that record will be considered as part of the output. In some cases, the output may be an empty set if no record satisfies either condition.

Example 2:

mysql> SELECT *FROM students WHERE Student_Age = 21 OR Student_Percentage = 94;

Output:

There are two records roll numbers '4,' and ‘5’ in the students table, which has either age equals to ‘21’ or percentage equals to ‘94’.

Example 3:

mysql> SELECT *FROM students WHERE Student_Gender = "Female" OR Student_HomeTown = "Chandigarh" OR Student_Age = 24;

Output:

Among all the records in the students table, there are three records with roll number ‘2’, ‘4’ and ‘5’, which has either gender as ‘female'; home town as ‘Chandigarh’ or age equals to ‘24’. These are the records that satisfy either of the three conditions.

Example 4:

mysql> SELECT *FROM students WHERE Student_Gender = "Male" OR  Student_Name = "Kartik Goenka" OR Student_Percentage = 92;

Output:

Among all the records in the students table, there are three records with roll numbers '1’, ‘2’ and ‘3’ in which either of the three conditions is met, i.e., gender as male, name equals to ‘Kartik Goenka’ or percentage equals to ‘92’.

• AND and OR operator with WHERE clause in a single query

In WHERE clause query with a SELECT statement, we can also use a combination of AND and OR operators in a single query. The purpose of using the combinations of AND and OR operators in a single query is to test the higher level of complicated conditions.

Syntax:

SELECT *FROM table_name WHERE Condition 1 AND/OR Condition 2 [AND/OR Condition 3];

Example 1:

mysql> SELECT *FROM students WHERE Student_Gender = "Female" AND Student_HomeTown = "Chandigarh" OR Student_Age = 24;

Output:

According to the conditions in a query, the record should have gender as 'female' and home town as 'Chandigarh'. The age of the student may or may not be ‘24’. So, there is only one record with roll number ‘4’, which meets these conditions. Even if any record has gender as ‘female’ and home town other than ‘Chandigarh’ or vice versa, then that record will not be considered in output.

Example 2:

SELECT * FROM students WHERE Student_Gender = "Female" OR Student_HomeTown = "Chandigarh" AND Student_Age = 24;

Output:

According to the conditions in a query, the record should have either gender as ‘female’ or home town as 'Chandigarh'. The age of the student should not be other than ‘24’. So, there are two records with roll number ‘4’ and ‘5’ which meet these conditions.

Example 3:

mysql> SELECT *FROM students WHERE Student_Gender = "Male" AND Student_HomeTown = "Lucknow" OR Student_Age = 23 AND Student_Percentage = 75;

Output:

There is only one record in students table with gender as 'male', the home town as ‘Lucknow’ and the percentage of the student should be '75'. The age of a student can be 23 or other than 23.

Example 4:

mysql> SELECT *FROM students WHERE Student_Gender = "Male" OR Student_HomeTown = "Lucknow" AND Student_Age = 23 OR Student_Percentage = 75;

Output:

In the students table, there are three records which have gender as 'male' and age as ‘23’. The home town may or may not be ‘Lucknow’. Also, the percentage may or may not be '75'.