w3resource

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

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

See our Model Database

Practice SQL Exercises

Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.

Previous: ALL
Next: EXISTS