×

Where Condition in SQL

WHERE clause is used with the Data Manipulation Language Command in SQL, the Data Manipulation Language commands are the SELECT statement, UPDATE statement, and DELETE statement.

WHERE clause condition is an optional part of the SQL Data Manipulation Language query

WHERE clause condition is used to filter the records in the SQL query, the WHERE clause condition return only those records, which fulfill the specific Condition in the SQL query.

We can use WHERE Condition in the SELECT statement, UPDATE statement, and DELETE statement. We can even use Logical operators and comparison operators with the SQL queries WHERE conditions.

Let’s take deep dive and understand WHERE conditions in SQL concept with the help of examples.

The syntax of WHERE Condition is as follows:

SELECT * FROM Table_Name [WHERE conditions];

WHERE clause is optional in the SQL query, you want selected rows to be fetched using WHERE Condition in the SQL query.

The syntax of WHERE Condition of selected columns from the table is as follows:

SELECT Column_Name1, Column_Name2 FROM Table_Name [WHERE conditions]; 

Consider the already existing table:

Table 1: Emp

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDGENDER
1001VAIBHAVIMISHRA65000PUNEORACLE1F
1002VAIBHAVSHARMA60000NOIDAORACLE1M
1003NIKHILVANI50000JAIPURFMW2M
2001PRACHISHARMA55500CHANDIGARHORACLE1F
2002BHAVESHJAIN65500PUNEFMW2M
2003RUCHIKAJAIN50000MUMBAITESTING4F
3001PRANOTISHENDE55500PUNEJAVA3F
3002ANUJAWANRE50500JAIPURFMW2F
3003DEEPAMJAUHARI58500MUMBAIJAVA3M
4001RAJESHGOUD60500MUMBAITESTING4M

Table 2: Manager.

ManageridManager_NameManager_department
1Snehdeep KaurORACLE
2Kirti KirtaneFMW
3Abhishek ManishJAVA
4Anupam MishraTESTING

1. WHERE Condition with SELECT statement

The SELECT statement is used to fetch the records from the SQL table. Assume you want to fetch those records from the Order table whose order is placed between 20 September to 25 September. In this situation, we have to use the WHERE clause in the SELECT statement and also BETWEEN operator with the WHERE clause. The WHERE clause is used when we want to retrieve records of specific conditions from the table.

Syntax of WHERE Condition with the SELECT statement:

SELECT * FROM Table_Name [WHERE conditions];

Example 1: Execute a query to display employee information from the Emp Table, only that Employee whose City is 'Pune’.

SELECT Employeeid, First_Name, Last_Name, City, Salary, Gender from Emp WHERE City = 'Pune';

In the above query, we display employee information from the Emp Table of employees in Pune city. In the WHERE condition, we used the Equal comparison operator, which will compare Pune city in the Condition.

The Following output for the above query:

Where Condition in SQL

Example 2:  Execute a query to display employee information from the Emp Table, only that Employee whose Salary is greater than 55000.

SELECT Employeeid, First_Name, Last_Name, Department, Salary, Gender from Emp WHERE Salary > 55000;

In the above query, we display the employee's information from the Emp Table of that Employee whose Salary is greater than 55000. In the WHERE condition, we used the GREATER THAN comparison operator, which will use to compare SalarySalary in the Condition.

The Following output for the above query:

Where Condition in SQL

Example 3: Execute a query to display employee information from the Emp Table, only those employees whose Salary is greater than 55000 or whose City is 'Pune’.

SELECT Employeeid, First_Name, Last_Name, Department, Salary, City, Gender from Emp WHERE Salary > 55000 OR City = ’Pune’;

In the above query, we display employee information from the Emp Table of those employees whose Salary is greater than 55000 OR City is 'Pune'. If an employee's salary is greater than 55000, it will return that Employee in the result set, OR if the employee's city is Pune, it will return that Employee in the result set. In the WHERE condition, we used OR operator, which will return only the true value of the Condition.

The Following output for the above query:

Where Condition in SQL

Example 4: Execute a query to display employee information from the Emp Table only for that Employee whose Salary is between 50000 and 60000.

SELECT Employeeid, First_Name, Last_Name, Department, Salary, City, Gender from Emp WHERE Salary BETWEEN 50000 AND 60000;

In the above query, we display employee information from the Emp Table of those employees whose Salary is between 55000 and 60000. In the WHERE condition, we used BETWEEN operators to return those employees whose Salary is between 50000 and 60000 in the result set.

The Following output for the above query:

Where Condition in SQL

Consider the following tables along with the given records.

Table 1: Emp

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDGENDER
1001VAIBHAVIMISHRA65000PUNEORACLE1F
1002VAIBHAVSHARMA60000NOIDAORACLE1M
1003NIKHILVANI50000JAIPURFMW2M
2001PRACHISHARMA55500CHANDIGARHORACLE1F
2002BHAVESHJAIN65500PUNEFMW2M
2003RUCHIKAJAIN50000MUMBAITESTING4F
3001PRANOTISHENDE55500PUNEJAVA3F
3002ANUJAWANRE50500JAIPURFMW2F
3003DEEPAMJAUHARI58500MUMBAIJAVA3M
4001RAJESHGOUD60500MUMBAITESTING4M

Table 2: Manager.

ManageridManager_NameManager_Department
1Snehdeep KaurORACLE
2Kirti KirtaneFMW
3Abhishek ManishJAVA
4Anupam MishraTESTING

2. WHERE Condition with an UPDATE statement

The UPDATE statement in SQL is used to modify the SQL Table records. We used the WHERE clause with the UPDATE statement to modify the specific records of the SQL Table.

The syntax of WHERE Condition with the UPDATE statement is as follows:

UPDATE Table_Name SET Column_Name = Value WHERE conditions;

Example 1: Execute a query to modify the Employee's first name as Harsha, whose employee id is 3002.

UPDATE Emp SET First_Name = 'HARSHA' WHERE Employeeid = 3002;

In the above query, we modify the Employee First_ Name as HARSHA from the Emp Table, but whose employee id is 3002.

We will cross-check the query using the SELECT statement to check whether the employee name is modified or not.

SELECT * FROM Emp WHERE EmployeeId = 3002; 
Where Condition in SQL

Example 2:  Execute a query to modify the Employee City as Hyderabad and Salary increased by 1.2% whose employee Department is Testing and Employee is male.

UPDATE Emp SET City = 'HYDERABAD', Salary = Salary * 1.2 WHERE Department = 'TESTING' AND Gender = 'M';

In the above query, we modify the Employee City as Hyderabad and increment Salary by 1.2%, but whose employee Department is testing and Employee must be male.

We will cross-check the query using the SELECT statement to check whether the employee name is modified or not.

SELECT * FROM Emp WHERE Department = 'TESTING' AND Gender = 'M';
Where Condition in SQL

3. WHERE Condition with DELETE statement

The DELETE statement in SQL is used to delete the records from the SQL Table. We used the WHERE clause with the DELETE statement to delete the specific records from the SQL Table.

The syntax of WHERE Condition with the DELETE statement is as follows:

DELETE FROM Table_Name WHERE conditions;

Example 1: Execute a query to delete the Employee from the Emp table whose employee city is Pune and Employee must be male.

DELETE FROM Emp WHERE City = 'Pune' AND Gender =' M';

In the above query, we removed the employee details from Emp Table, whose City is Pune, and Employee must be male.

We will cross-check the query using the SELECT statement to check whether the employee name is deleted or not.

SELECT * FROM Emp;
Where Condition in SQL

Example 2: Execute a query to delete the Employee from the Emp table whose employee city is Mumbai or whose Salary is greater than 50000.

DELETE FROM Emp WHERE City = 'Pune' AND Gender =' M';

In the above query, we deleted the employee details from Emp Table, whose City is Mumbai, and the employee Salary is greater than 50000.

We will cross-check the query using the SELECT statement to check whether the employee name is deleted or not.

SELECT * FROM Emp;
Where Condition in SQL

Related Topics

SQL SELECT Statement

The SQL SELECT statement is used to retrieve the data from the tables. We can also retrieve the selected records from the table using the query condition. The SQL SELECT...

5 minutes read.

SQL Commands

SQL commands are classified into four groups on the basis of their nature. DDL (Data Definition Language) DML (Data Manipulation Language) DCL (Data Control Language) DQL (Data Query Language) NOTE: This...

1 minute read.

SQL SELECT WHERE Clause

In this SQL section, we will help you understand the concept of the SQL SELECT WHERE clause with a few examples. The WHERE clause in SELECT query displays those records as...

5 minutes read.

Check Constraint in SQL

The Check Constraint in SQL is the rule or set of rules used to limit the data range that can be entered in a table column. Check constraint is used...

8 minutes read.

SQL Left Join

The SQL Left Join query displays all the records from the table and displays similar records from the right table. The query displays zero records if it doesn’t find any...

4 minutes read.

SQL Arithmetic Operators

This page contains all the information, learn about SQL Arithmetic Operators concept in the SQL table with the help of examples. The Arithmetic Operators is used to perform mathematical calculations on...

5 minutes read.

SQL Scalar Functions

TEACHER Table LCASE() Function The LCASE() function is used to return lower case values of specified column. Syntax: Select Lcase(column_name) from table_name; Example: Select Lcase(teacher_name) from teacher; Syntax: Select Lcase(column_name) from table_name [where condition]; Example: Select Lcase(teacher_name) from teacher where teacher_id=1; UCASE() Function The UCASE()...

1 minute read.

SQL Alter Table

In Structured Query Language, if you want to add columns in an existing table, then modify the table, or delete columns from the table. All these operations are allowed only...

7 minutes read.

How to use INNER JOIN in SQL

In this article, we will learn about the INNER JOIN concept and how to use it in SQL with the WHERE clause. What is INNER JOIN in SQL? Inner Join is a...

6 minutes read.

Update Query in SQL

Update is an SQL command that is used to modify the data that in already present in database. Update is a command of DML. DML means Data Manipulation Language. Basically,...

3 minutes read.

SQL INSERT INTO Values

This article will help you in getting a better understanding of a very important SQL INSERT INTO VALUES function. INSERT INTO statement is used to insert or add a new record...

4 minutes read.

SQL NOT Operator

SQL NOT is a Boolean operator used with the WHERE clause. NOT operator shows the records if the expression is false. When we use the NOT operator, we fetch only...

7 minutes read.

SQL Topological Sorting

It is for Directed Acyclic Graph, which linearly describes the ordering of graphs. It is not possible for all graphs, it is possible for only Directed acyclic graphs. Applications of Topological...

3 minutes read.

SQL Aggregate Functions

In this tutorial, we will learn and understand the SQL Aggregate Functions concept with the help of examples. SQL Aggregate Function is a function used to perform calculation operations on one...

4 minutes read.

SQL Auto Increment

As we all know, In SQL for unique identification, we assign a column with the primary key. But in some tables, we find difficult to differentiate a column as a...

5 minutes read.

SQL GROUP BY CLAUSE

The GROUP BY clause is used to arrange similar records into the groups in the Structured Query Language queries. The records are arranged with the help of functions which is...

4 minutes read.

SQL CASE

This page contains all the information about SQL CASE. The CASE is an If-Else type of logical query used in the statement. The CASE in Structured Query Language is similar...

5 minutes read.

Drop vs Truncate in SQL

In this article, we will learn and understand the Drop and Truncate commands and the difference between these two commands. What is Drop Command? Drop is a Data Definition Language command in...

6 minutes read.

SQL SELECT IN

SQL SELECT IN is a logical operator in Structured Query Language. It is used in SQL queries to reduce the use of multiple 'OR' operators. s The IN operator in SQL...

6 minutes read.

TCL Commands in SQL

In Structured Query Language, TCL is an abbreviation for Transaction Control Language. A single unit of work in a database is formed after the consecutive execution of commands is known...

6 minutes read.