SQL SOME Operator
SOME Operator
SOME compare a value to each value in a list or results from a query and evaluate to true if the result of an inner query contains at least one row. SOME must match at least one row in the subquery and must be preceded by comparison operators. Suppose using greater than ( >) with SOME means greater than at least one value.
Syntax:
SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} ( subquery )
Parameters:
| Name | Description |
|---|---|
| column_name | Name of the column of the table. |
| expression1 | Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations. |
| table_name | Name of the table. |
| WHERE expression2 | Compares a scalar expression until a match is found for SOME operator. One or more rows must match the expression to return a Boolean TRUE value for the SOME operator. |
| comparison_operator | Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=). |
Pictorical Presentation: SQL SOME Operator
DBMS Support: SOME Operator
| DBMS | Command |
| MySQL | Supported |
| PostgreSQL | Supported |
| SQL Server | Supported |
| Oracle | Supported |
Example: SQL SOME Operator
To get 'agent_code', 'agent_name', 'working_area', 'commission' from 'agents' table with following conditions -
1. 'agent_code' should be within some 'agent_code' from 'customer' table, which satisfies the condition bellow:
a) 'cust_country' in the 'customer' table must be 'UK',
the following SQL statement can be used :
SQL Code:
SELECT agent_code,agent_name,working_area,commission
FROM agents
WHERE agent_code=SOME(
SELECT agent_code FROM customer
WHERE cust_country='UK');Sample table: agents
Sample table: customer
Output:
AGENT_CODE AGENT_NAME WORKING_AREA COMMISSION ---------- -------------------- -------------------- ---------- A009 Benjamin Hampshair .11 A003 Alex London .13 A006 McDen London .15
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
SQL: Tips of the Day
MySQL select 10 random rows from 600K rows fast:
SELECT name
FROM random AS r1 JOIN
(SELECT CEIL(RAND() *
(SELECT MAX(id)
FROM random)) AS id)
AS r2
WHERE r1.id >= r2.id
ORDER BY r1.id ASC
LIMIT 1
Ref: https://bit.ly/3GdCTM3
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook