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.
SELECT column_name(s) FROM table_name WHERE expression comparison_operator SOME (subquery)
Sample Queries and Outputs:
select name from instructor where Salary > some(select Salary from instructor where dept='Computer Science');
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.
This article is contributed by Anannya Uberoi. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- SQL | Procedures in PL/SQL
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between SQL and T-SQL
- Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)
- SQL | SELECT Query
- SQL | Distinct Clause
- SQL | WHERE Clause
- SQL | AND and OR operators
- SQL | INSERT INTO Statement
- SQL | DELETE Statement
- SQL | UPDATE Statement
- SQL | SELECT TOP Clause
- SQL | ORDER BY
- SQL | Aliases
- SQL | Wildcard operators
- SQL | Join (Inner, Left, Right and Full Joins)
- SQL | Union Clause
- SQL | Join (Cartesian Join & Self Join)
- SQL | CREATE