In sql, the SOME operator is used to compare a value with single column set of values returned by subquery. The SOME operator in sql must match at least one value in subquery and that value must be preceded by comparison operators.
Generally we will use this SOME operator in WHERE clause to check whether the required column values are matching with the set of values returned by subquery or not.
Following is the syntax of using a some operator in sql server.
SELECT column1,column2 FROM tablename WHERE column1 = SOME(SELECT column1 FROM tablename WHERE column1 ='somevalue')
If you observe above sql SOME operator syntax, we will get a values only when column1 values matches with column1 data returned by subquery otherwise it will not return any data.
We will check some operator with examples for that create “EmployeeDetails” table by using following script in your sql database.
create table EmployeeDetails(empid int, empname varchar(50),designation varchar(50),salary int,Location varchar(50))
insert into EmployeeDetails
select * from EmployeeDetails
Once we execute above script, a new table called “EmployeeDetails” will be created and result will be like as shown below.
Now execute following examples to check how SOME operator will work in sql server.
Following sql statement will return an employee details whose salary column values matches with the data returned by subquery.
SELECT * FROM EmployeeDetails WHERE salary = SOME(SELECT salary FROM EmployeeDetails WHERE salary >25000)
When we execute above sql query, we will get the result will be like as shown below.
Following sql statement will return an employee details whose salary column value matches with at least one value with the data returned by subquery and that value must be preceded by comparison operators.
SELECT * FROM EmployeeDetails WHERE salary > SOME(SELECT salary FROM EmployeeDetails WHERE salary >25000)
When we execute above query, we will get the result like as shown below.