SQL Some Operator

In SQL, the SOME operator is used to compare a value with a single column set of values returned by the subquery. The SOME operator in SQL must match at least one value in a 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.

Syntax of SQL SOME Operator

Following is the syntax of using a some operator in the 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 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

values(1,'suresh','software engineer',25000,'chennai'),

(2,'rohini','AEO',15000,'chennai'),

(3,'madhavsai','business analyst',50000,'nagpur'),

(4,'mahendra','CA',75000,'guntur'),

(5,'sateesh','Doctor',65000,'guntur')

 

select * from EmployeeDetails

When we execute the above script, a new table called “EmployeeDetails” will be created and the result will be as shown below.

 

Newly created employeedetails table in sql server

Now execute following examples to check how SOME operator will work in SQL server.

SQL SOME Operator Example

The following sql statement will return employee details whose salary column values match with the data returned by the 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.

 

SQL Some Operator Example Result or Output

SQL Some Operator Example2

The following sql statement will return 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 the above query, we will get the result as shown below.

 

SQL Some Operator Example Result or Output