SQL | SOME
Last Updated :
21 Mar, 2018
SQL | ALL and ANY
SOME operator evaluates the condition between the outer and inner tables and evaluates to true if the final result returns any one row. If not, then it evaluates to false.
- The SOME and ANY comparison conditions are similar to each other and are completely interchangeable.
- SOME must match at least one row in the subquery and must be preceded by comparison operators.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE expression comparison_operator SOME (subquery)
Instructor Table:
Name |
Department |
Salary |
Chandra |
Computational Biology |
1 |
Visweswaran |
Electronics |
1.5 |
Abraham |
Computer Science |
1.3 |
John |
Electronics |
1.2 |
Samantha |
Computer Science |
2 |
Jyoti |
Electronics |
1.2 |
Debarka |
Computer Science |
2 |
Ganesh |
Computational Biology |
0.9 |
Sample Queries and Outputs:
select name
from instructor
where Salary > some(select Salary
from instructor
where dept='Computer Science');
Output:
Visweswaran |
Samantha |
Debarka |
Explanation
The instructors with salary > (salary of some instructor in the ‘Computer Science’ department) get returned. The salaries in the ‘Computer Science’ department are 1.3, 2 and 2. This implies any instructor with a salary greater than 1.3 can be included in the final result.
Exercise: Try to write same query using ANY clause.
Share your thoughts in the comments
Please Login to comment...