SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL 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

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 KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

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

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



ADVERTISEMENT
ADVERTISEMENT