SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

SQL WHERE Multiple Conditions
2022-04-11 10:25:03

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_RollNoStudent_NameStudent_GenderStudent_MobileNumberStudent_HomeTownStudent_AgeStudent_Percentage
1Rohit MoreMale9890786123Lucknow2375
2Kunal ShahMale7789056784Chandigarh2068
3Kartik GoenkaMale9908743576Ahemdabad2292
4Anupama ShahFemale8890907656Chandigarh2494
5Snehal JainFemale8657983476Surat2194

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

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

Output:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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:

SQL WHERE Multiple Conditions

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'.