

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Special Operators in SQL
The different special operators in SQL are as follows −
- ALL operator
- ANY Operator
- BETWEEN Operator
- EXISTS Operator
- IN Operator
- LIKE Operator
Now let us create a table to understand the examples of special operators −
<Employee>
Emp_ID | Emp_Name | Emp_Salary | Emp_DeptID | Emp_DeptName |
---|---|---|---|---|
1 | Aaron | 10000 | 10 | Technical |
2 | Harry | 12000 | 20 | Operations |
3 | Mary | 5000 | 30 | Finance |
4 | Angel | 55000 | 10 | Technical |
5 | Will | 20000 | 30 | Finance |
<Dependents>
Dep_ID | Emp_ID | Dep_Name | Dep_Age |
---|---|---|---|
1001 | 2 | Keith | 88 |
1002 | 3 | Kim | 5 |
1003 | 5 | Lucy | 90 |
Details of all the special operators using the above tables are −
ALL operator
The ALL operator compares a value with all the values returned by the subquery and is true only if the given condition is satisfied for all the values. For example −
Select * from Employee Where Emp_Salary > ALL (select Emp_Salary from Employee where Emp_DeptID=30);
This query returns the details of all the employees whose salary is greater than the salary of all the employees in Department number 30 i.e. Angel in this case.
Emp_ID | Emp_Name | Emp_Salary | Emp_DeptID | Emp_DeptName |
---|---|---|---|---|
4 | Angel | 55000 | 10 | Technical |
ANY operator
The ANY operator compares a value with all the values returned by the subquery and is true only if the given condition is satisfied for any value in the set of values. For example −
Select * from Employee Where Emp_salary > ANY (select Emp_Salary from Employee where Emp_DeptID=30);
This query returns the details of all the employees whose salary is greater than the salary of even one employee in Department number 30 i.e. Aaron, Harry, Angel and Will in this case.
Emp_ID | Emp_Name | Emp_Salary | Emp_DeptID | Emp_DeptName |
---|---|---|---|---|
1 | Aaron | 10000 | 10 | Technical |
2 | Harry | 12000 | 20 | Operations |
4 | Angel | 55000 | 10 | Technical |
5 | Will | 20000 | 30 | Finance |
BETWEEN operator
The BETWEEN operator returns the information within a given range of values, where the minimum and maximum of the range is specified. For example −
Select * from Employee Where Emp_Salary BETWEEN 20000 AND 60000;
This query returns information about all the employees whose salary is between the range of 20000 and 60000 i.e. Angel and Will in this case.
Emp_ID | Emp_Name | Emp_Salary | Emp_DeptID | Emp_DeptName |
---|---|---|---|---|
4 | Angel | 55000 | 10 | Technical |
5 | Will | 20000 | 30 | Finance |
EXISTS operator
The EXISTS operator only returns true if the subquery returns at least one record i.e if some data exists for the given subquery. For example −
Select * from Employee Where EXISTS (select * from Dependents where Employee.Emp_ID=dependents.Emp_ID);
This query will return results for only those employees who have any dependents i.e. Harry, Mary and Will.
Emp_ID | Emp_Name | Emp_Salary | Emp_DeptID | Emp_DeptName |
---|---|---|---|---|
2 | Harry | 12000 | 20 | Operations |
3 | Mary | 5000 | 30 | Finance |
5 | Will | 20000 | 30 | Finance |
IN operator
The IN operator is true if the query results in values that are contained in the list of constant values for the IN operator. For example −
Select * from Employee Where Emp_ID IN (1,2,5);
This query gives details about the employees whose Employee number is 1,2 or 5 i.e Aaron, Harry and Will.
Emp_ID | Emp_Name | Emp_Salary | Emp_DeptID | Emp_DeptName |
---|---|---|---|---|
1 | Aaron | 10000 | 10 | Technical |
2 | Harry | 12000 | 20 | Operations |
5 | Will | 20000 | 30 | Finance |
LIKE operator
The LIKE operator is used to select the values that match the patterns specified in the query. Two wildcard operators are used for this. For example −
Select * from Employee Where Emp_Name LIKE “A%”
This query returns the data of all the employees whose name starts with A i.e. Aaron and Angel in our example.
Emp_ID | Emp_Name | Emp_Salary | Emp_DeptID | Emp_DeptName |
---|---|---|---|---|
1 | Aaron | 10000 | 10 | Technical |
4 | Angel | 55000 | 10 | Technical |
- Related Questions & Answers
- String Special Operators in Python
- Types of Join and Set Operators in SQL
- Explain different comparison operators used in SQL queries
- Difference between SQL and PL/SQL
- Difference Between SQL and T-SQL
- Special Characters in HTML
- Special arrays in JavaScript
- Difference between SQL(Structured Query Language) and T-SQL(Transact-SQL).
- Subquery in SQL
- Perl Special Literals
- Difference between Open SQL, Native SQL in SAP HANA
- Difference between Static SQL and Dynamic SQL
- Difference Between T-SQL and PL-SQL
- Sort data in SQL using Dynamic SQL in SAP HANA
- The $[ Special Variable in Perl