SQL Reference

Example

SELECT * FROM Employees WHERE SOME (SELECT Salary FROM Employees) > 1000;

Output

EmployeeId	LastName	FirstName	Salary
1	Smith	John	2000
2	Doe	Jane	1500

Explanation

The SQL code is selecting all columns from employees where at least one employee’s salary is over 1000. The SOME statement returns true if the condition is true for any pair in the set.

Example

SELECT ProductName
FROM Products
WHERE ProductID = SOME (SELECT ProductID FROM OrderDetails WHERE Quantity > 10);

Output

ProductName
-----------
Chai
Chang
Aniseed Syrup

Explanation

The SOME operator is used in the WHERE clause and returns true if the comparison between ProductID from the Products table and any ProductID meeting the condition (Quantity > 10) in the subquery is true. In the result set, we see the ProductNames for which this condition holds true. The SOME keyword can be replaced with ANY without affecting the result in SQL Server.

Example

SELECT department_id FROM employees
WHERE department_id = SOME (SELECT department_id FROM employees WHERE salary > 10000);

Output

DEPARTMENT_ID
-------------
          100
          200
          300
          400

Explanation

The “SOME” SQL operator returns true if the comparison between a scalar value and a single column subquery is met for some value of the subquery. In this example, the query is returning the IDs of departments where at least one employee has a salary greater than 10,000.