T-SQL - SOME Operator

The T-SQL (Transact-SQL) SOME Operator is used with WHERE and HAVING clause. It returns TRUE if any of the subquery values satisfies the condition.

Syntax

The syntax for using SOME operator in T-SQL (Transact-SQL) is given below:

SELECT column1, column2, column3, ...
FROM table_name
WHERE column_name operator SOME
(SELECT column1, column2, ... 
FROM table_name WHERE condition);

Note: The operator mentioned above must be a standard comparison operator (=, <>, !=, <, <=, > or >=).

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

Table 2: Contact_Info table

Phone_Number	EmpID	Address	Gender
+1-8054098000	2	Brooklyn, New York, USA	F
+33-147996101	3	Grenelle, Paris, France	M
+31-201150319	4	Geuzenveld, Amsterdam, Netherlands	F
+86-1099732458	6	Yizhuangzhen, Beijing, China	M
+65-67234824	7	Yishun, Singapore	M
+81-357799072	8	Koto City, Tokyo, Japan	M

To select records of male employees from Employee table, the query is given below.
```
SELECT * FROM Employee
WHERE EmpID = SOME
(SELECT EmpID 
FROM Contact_Info WHERE Gender = 'M');
```
The subquery will produce following result:

EmpID

3

6

7

8

The query then compares EmpID values with subquery result. It returns TRUE as few records satisfy the condition and produces the following result:

EmpID Name City Age Salary

3 Jo Paris 27 2800

6 Huang Beijing 28 2800

EmpID
3
6
7
8

EmpID	Name	City	Age	Salary
3	Jo	Paris	27	2800
6	Huang	Beijing	28	2800

❮ T-SQL - Operators