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

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID	GENDER
1001	VAIBHAVI	MISHRA	65000	PUNE	ORACLE	1	F
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE	1	M
1003	NIKHIL	VANI	50000	JAIPUR	FMW	2	M
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1	F
2002	BHAVESH	JAIN	65500	PUNE	FMW	2	M
2003	RUCHIKA	JAIN	50000	MUMBAI	TESTING	4	F
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3	F
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2	F
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3	M
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4	M

Table 2: Manager.

Managerid	Manager_Name	Manager_department
1	Snehdeep Kaur	ORACLE
2	Kirti Kirtane	FMW
3	Abhishek Manish	JAVA
4	Anupam Mishra	TESTING

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:

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:

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:

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:

Consider the following tables along with the given records.

Table 1: Emp

EMPLOYEEID	FIRST_NAME	LAST_NAME	SALARY	CITY	DEPARTMENT	MANAGERID	GENDER
1001	VAIBHAVI	MISHRA	65000	PUNE	ORACLE	1	F
1002	VAIBHAV	SHARMA	60000	NOIDA	ORACLE	1	M
1003	NIKHIL	VANI	50000	JAIPUR	FMW	2	M
2001	PRACHI	SHARMA	55500	CHANDIGARH	ORACLE	1	F
2002	BHAVESH	JAIN	65500	PUNE	FMW	2	M
2003	RUCHIKA	JAIN	50000	MUMBAI	TESTING	4	F
3001	PRANOTI	SHENDE	55500	PUNE	JAVA	3	F
3002	ANUJA	WANRE	50500	JAIPUR	FMW	2	F
3003	DEEPAM	JAUHARI	58500	MUMBAI	JAVA	3	M
4001	RAJESH	GOUD	60500	MUMBAI	TESTING	4	M

Table 2: Manager.

Managerid	Manager_Name	Manager_Department
1	Snehdeep Kaur	ORACLE
2	Kirti Kirtane	FMW
3	Abhishek Manish	JAVA
4	Anupam Mishra	TESTING

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; 

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

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;

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;