Skip to main content

FOR SOME (SQL)

Determines whether to return a record based on a condition test of field values.

Synopsis

FOR SOME (table [AS t-alias]) (fieldcondition)

Description

The FOR SOME predicate allows you to determine whether to return a record based on a boolean condition test of the values of one or more fields in a table. If fieldcondition evaluates as true, the record is returned. If fieldcondition evaluates as false, the record is not returned.

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

Delimiting parentheses are mandatory for the table (and its optional t-alias) argument. Delimiting parentheses are also mandatory for the fieldcondition argument. Whitespace is permitted, but not required, between these two sets of parentheses.

Commonly, FOR SOME is used to determine whether to return a record from a table based on the contents of a record in another table. FOR SOME can also be used to determine whether to return a record from a table based on the contents of a record in the same table. In this latter case, either all records are returned or no records are returned.

Compound Conditions

A fieldcondition can contain more than one condition expression. The set of conditions is enclosed in parentheses. Multiple conditions are specified with the logical operators AND and OR, which can also be specified using the & and ! symbols. A logical operator may be followed by the NOT unary operator. By default, conditions are evaluated in left-to-right order. You can specify a different order of evaluation by grouping multiple conditions using parentheses.

SELECT Name,COUNT(Name) AS NameCount
FROM Sample.Person AS p
WHERE FOR SOME (Sample.Employee AS e)(e.Name=p.Name AND p.Name %STARTSWITH 'A')
ORDER BY Name
SELECT Name,COUNT(Name) AS NameCount
FROM Sample.Person AS p
WHERE FOR SOME (Sample.Employee AS e)(e.Name=p.Name OR  p.Name %STARTSWITH 'A')
ORDER BY Name

Multiple Tables

You can specify multiple tables as a comma-separated list before the fieldcondition. The condition that determines whether to return records may reference the table from which data is being selected, or may reference field values in another table. Table aliases are usually required to associate each specified field with its table.

Arguments

table

table can be a single table or a comma-separated list of tables. The enclosing parentheses are mandatory.

AS t-alias

An optional alias for the preceding table name. An alias must be a valid identifier; it can be a delimited identifier.

fieldcondition

fieldcondition specifies one or more condition expressions referencing one or more fields. Thefieldcondition is enclosed with parentheses. You can specify multiple condition expressions within fieldcondition using AND (&) and OR (!) logical operators.A subquery, enclosed in parentheses, which returns a result set from a single column that is used for the comparison with scalar-expression.

Examples

In the following example, FOR SOME returns all records in the Sample.Person table in which its Name field value matches the Name field value in the Sample.Employee table:

SELECT Name,COUNT(Name) AS NameCount
FROM Sample.Person AS p
WHERE FOR SOME (Sample.Employee AS e)(e.Name=p.Name)
ORDER BY Name

In the following example, FOR SOME returns records in the Sample.Person table based on a boolean test of the same table. This program returns all Sample.Person records if at least one record has an Age value greater than 65. Otherwise, it returns no records. Because at least one record in Sample.Person has an Age field value greater than 65, all Sample.Person records are returned:

SELECT Name,Age,COUNT(Name) AS NameCount
FROM Sample.Person
WHERE FOR SOME (Sample.Person)(Age>65)
ORDER BY Age

Like most predicates, FOR SOME can be inverted using the NOT logical operator, as shown in the following example:

SELECT Name,Age,COUNT(Name) AS NameCount
FROM Sample.Person
WHERE NOT FOR SOME (Sample.Person)(Age>65)
ORDER BY Age

In the following example, FOR SOME returns all records in the Sample.Person table in which its Name field value matches the Name field value in the Sample.Employee table, and their residence (Home_State) is in the same state as their office (Office_State):

SELECT Name,Home_State,COUNT(Name) AS NameCount
FROM Sample.Person AS p
WHERE FOR SOME (Sample.Employee AS e)(p.Name=e.Name AND p.Home_State=e.Office_State)
ORDER BY Name

In the following example, all records are returned if there is at least one Name in the Sample.Person table that is also found in the Sample.Employee table. Because this condition is true for at least one record, all Sample.Person records are returned:

SELECT Name AS PersonName,Age,COUNT(Name) AS NameCount
FROM Sample.Person
WHERE FOR SOME (Sample.Employee AS e,Sample.Person AS p) (e.Name=p.Name)
ORDER BY Name

In the following example, all records are returned if there is at least one Name in the Sample.Person table that is also found in the Sample.Company table. Because names of persons and names of companies (in this data set) are never the same, this condition is not true for any record. Therefore, no Sample.Person records are returned:

SELECT Name AS PersonName,Age,COUNT(Name) AS NameCount
FROM Sample.Person
WHERE FOR SOME (Sample.Company AS c,Sample.Person AS p) (c.Name=p.Name)
ORDER BY Name

See Also

FeedbackOpens in a new tab