SQL SOME

What does the SQL SOME operator do?

SOME compares a scalar value to each value returned by a subquery and returns TRUE if the comparison is TRUE for at least one row.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SOME: PostgreSQL, MySQL, SQL Server, Oracle, SQLite (from 3.35), Snowflake, BigQuery

SQL SOME Full Explanation

SOME (a synonym for ANY in most implementations) is a quantified comparison operator defined by the SQL standard. It bridges scalar-to-set comparison by evaluating a boolean predicate between a left-hand scalar expression and every value produced by the subquery on the right-hand side. If the predicate is TRUE for at least one row, the overall expression yields TRUE. If the subquery returns no rows, the result is UNKNOWN (NULL). If the predicate is never TRUE but at least one comparison evaluates to FALSE or UNKNOWN, the result is FALSE or UNKNOWN respectively.Permitted comparison operators are =, != (or <>), <, <=, >, and >=. SOME cannot be used with BETWEEN, LIKE, or IN directly. Because SOME is a quantifier, it must follow the operator: expr operator SOME (subquery). The subquery must return exactly one column, and it is implicitly coerced to the datatype of the left-hand expression when possible.SOME is useful when you need at least one match but do not want the strictness of ALL. It differs from IN because IN tests for equality only, whereas SOME allows any comparison operator.

SQL SOME Syntax

<scalar_expression> <comparison_operator> SOME (
    <subquery_returning_single_column>
);

SQL SOME Parameters

Example Queries Using SQL SOME

-- Is a product priced higher than at least one competitor?
SELECT product_id, price
FROM products p
WHERE price > SOME (
    SELECT price
    FROM competitor_prices c
    WHERE c.product_id = p.product_id
);

-- Find employees whose salary equals any salary in the Sales department
SELECT employee_id, salary
FROM employees
WHERE salary = SOME (
    SELECT salary
    FROM employees
    WHERE department = 'Sales'
);

-- Compare a constant to a subquery
SELECT 10 > SOME (SELECT age FROM users WHERE active = true) AS older_than_some;

Expected Output Using SQL SOME

Each query returns the rows (or scalar TRUE/FALSE/NULL) for which the comparison succeeds against at least one value from the subquery result set

Use Cases with SQL SOME

Checking if a value exceeds at least one value in another table
Creating flexible filters that accept any qualifying match rather than all matches
Replacing multiple OR conditions with a single quantified comparison
Writing standards-compliant code where ANY is reserved or unsupported

Common Mistakes with SQL SOME

Confusing SOME with ALL (ALL requires the predicate to be TRUE for every row)
Using SOME without a comparison operator (illegal syntax)
Expecting SOME to work with subqueries that return multiple columns
Forgetting that an empty subquery result yields UNKNOWN, not FALSE
Assuming SOME and IN are identical (IN is equality only)

First Introduced In

SQL-92 Standard

Frequently Asked Questions

Is SOME different from ANY?

No. They are interchangeable keywords in the SQL standard, and nearly all databases treat them as synonyms.

What result does SOME return when the subquery is empty?

The expression evaluates to UNKNOWN (effectively NULL), which behaves like FALSE in WHERE clauses unless you use IS UNKNOWN.

Can SOME be used with multiple-column subqueries?

No. The subquery must return exactly one column so the comparison is unambiguous.

When should I use SOME instead of IN?

Use SOME when you need operators other than equality, such as greater than or less than. IN only checks for equality.