Skip to main content

SOME (SQL)

Matches a value with at least one matching value from a subquery.

Synopsis

scalar-expression comparison-operator SOME (subquery)

Description

The SOME keyword works in conjunction with a comparison operator to create a predicate (a quantified comparison condition) that is true if the value of a scalar expression matches one or more of the corresponding values retrieved by the subquery. The SOME predicate compares a single scalar-expression item with a single subquery SELECT item. A subquery with more than one select item generates an SQLCODE -10 error.

Note:

The SOME and ANY keywords are synonyms.

SOME can be used wherever a predicate condition can be specified, as described in Overview of Predicates.

Arguments

scalar-expression

A scalar expression (most commonly a data column) whose values are being compared with the result set generated by subquery.

comparison-operator

One of the following comparison operators: = (equal to), <> or != (not equal to), < (less than), <= (less than or equal to), > (greater than), >= (greater than or equal to), [ (contains), or ] (follows).

subquery

A subquery, enclosed in parentheses, which returns a result set that is used for the comparison with scalar-expression.

Example

The following example selects those employees with salaries greater than $75,000 that live in any of the states west of the Mississippi River:

SELECT Name,Salary,Home_State FROM Sample.Employee
WHERE Salary > 75000
AND Home_State = SOME
 (SELECT State FROM Sample.USZipCode
  WHERE Longitude < -93)
ORDER BY Home_State

See Also

Purpose of this instance: Draft documentation
DocReleaseID: HealthConnect2025.1
Content loaded from:
/staging/learning/iris-doc/2025.1.x/doc/cache/en-us/src/
/staging/learning/iris-doc/2025.1.x/doc/ensemble/en-us/src/
/staging/learning/iris-doc/2025.1.x/doc/healthconnect/en-us/src/
/staging/learning/iris-doc/2025.1.x/doc/healthcommon/en-us/src/
/staging/learning/iris-doc/2025.1.x/doc/security/en-us/src/
Excluded items: AADAN, ABIT, ACLOUD, AFL_adonet, AFL_ecp, AFL_icm, AFL_jdbc, AFL_netxep, AFL_sharding, AFL_xep, APMML, APOWER, ARGATEWAY, AWEBSOCKETS, AWSGI, BJAVA, BJAVNAT, BJAVPER, BJAVXEP, BJSNAT, BNET, BNETNAT, BNETODBC, BNETXEP, BPYNAT, BSPK, BTPI, BXCI, GAUTOML, GCONV, GCSP, GICM, GIKNOW, GIML, GISQ, GISR, GSCF, GSQLSRCH, GTSQ, GUIMA, PAGE_activex, PAGE_administration, PAGE_apimgr, PAGE_appdev_ides, PAGE_bi, PAGE_cachedirect, PAGE_data_migration, PAGE_data_science, PAGE_deployment, PAGE_deployment_configuration, PAGE_deployment_containers, PAGE_deployment_icm, PAGE_deployment_iko, PAGE_deployment_install, PAGE_dotnet, PAGE_dotnet_native, PAGE_dsvr, PAGE_embedded_language_development, PAGE_epython, PAGE_external_language_development, PAGE_interop, PAGE_interop_lifecycle, PAGE_interop_vdoc, PAGE_interoperability, PAGE_java, PAGE_java_jdbc, PAGE_java_native, PAGE_machine_learning, PAGE_monitoring, PAGE_multimodel, PAGE_multimodel_multidimensional, PAGE_multimodel_object, PAGE_multivalue, PAGE_native, PAGE_nodejs_native, PAGE_objectscript, PAGE_objectscript_ides, PAGE_odbc, PAGE_platform_arch_design, PAGE_pygate, PAGE_python, PAGE_python_native, PAGE_reports, PAGE_scalability, PAGE_security_authentication, PAGE_security_authorization, PAGE_studio, PAGE_zen, PAGE_zenmojo, PAGE_zenreports
FeedbackOpens in a new tab