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

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: ALL
Next: EXISTS



Follow us on Facebook and Twitter for latest update.

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

 





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