SOME Operator 

SOME Operator in SQL Server

In this article, I will discuss the use of SOME Operator in SQL Server

The SOME Operator compare a value to each value in a list of results from a query and evaluate to true if the result of an inner query contains at least one row. SOME must match at least one row in the subquery and must be preceded by comparison operators. Suppose using greater than (>) with SOME means greater than at least one value.

Use below script to create database EmployeeDB, create PermanentEmployee and ContractEmployee table and populate these two tables with some test data.

-- Create a databale
CREATE DATABASE EmployeeDB
GO

Use EmployeeDB
GO

--Create PermanentEmployee Table
CREATE TABLE PermanentEmployee
(
  EmployeeId INT,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  Department varchar(50),
  Age INT
)
GO

--Insert Rows into PermanentEmployee Table
INSERT INTO PermanentEmployee VALUES (1,'Pranaya','Male','IT',20)
INSERT INTO PermanentEmployee VALUES (2,'Priyanka','Female','IT',22)
INSERT INTO PermanentEmployee VALUES (3,'Sudhanshu','Male','HR',25)
INSERT INTO PermanentEmployee VALUES (4,'Subrat','Male','Sales',60)
INSERT INTO PermanentEmployee VALUES (5,'Tarun','Male','Sales',54)
INSERT INTO PermanentEmployee VALUES (6,'Lipika','Female','HR',27)
INSERT INTO PermanentEmployee VALUES (7,'Smita','Female','IT',40)
INSERT INTO PermanentEmployee VALUES (8,'Smith','Male','HR',29)
GO
Fetch the records:

SELECT * FROM PermanentEmployee

SOME Operator in SQL Server

-- Create ContractEmployee Table
CREATE TABLE ContractEmployee
(
  EmployeeId INT,
  Name VARCHAR(50),
  Gender VARCHAR(50),
  Department varchar(50),
  Age INT
)
GO

--Insert rows into ContractEmployee Table
INSERT INTO ContractEmployee VALUES (9,'Anurag','Male','IT',33)
INSERT INTO ContractEmployee VALUES (11,'Sambit','Male','HR',29)
INSERT INTO ContractEmployee VALUES (12,'James','Male','Sales',37)
INSERT INTO ContractEmployee VALUES (13,'Pam','Female','Sales',25)
GO
Fetch the records:

SELECT * FROM ContractEmployee

SOME Operator in SQL Server

Syntax:

SELECT [column_name... | expression1 ]
FROM [table_name]
WHERE expression2 comparison_operator {ALL | ANY | SOME} (subquery)
Parameters:

column_name: Name of the column of the table.

expression1: Expression made up of a single constant, variable, scalar function, or column name and can also be the pieces of a SQL query that compare values against other values or perform arithmetic calculations.

table_name: Name of the table.

WHERE expression2: Compares a scalar expression until a match is found for SOME operator. One or more rows must match the expression to return a Boolean TRUE value for the SOME operator.

comparison_operator: Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Let’s understand SOME Operator with examples.
Scenario:

Consider a scenario where we have two tables such as PermanentEmployee and ContractEmployee. Both the tables have the column Age. If you need to get all the records from PermanentEmployee table where the Age is at-least greater than one value from the Age column of ContractEmployee table. What would be your query?

Solution:

We can use subquery and MIN function to write our query for above requirement as shown below.

SELECT * FROM PermanentEmployee

WHERE Age > ANY (SELECT MIN(Age) FROM ContractEmployee)

The above query will produce the below result.

SOME Operator in SQL Server

Use SOME Operator to get required results.

We can also use SOME instead of using Min function with the subquery.  As we want to get all rows from PermanentEmployee where Age is greater than any value of Age column in ContractEmployee, We will use > SOME. Greater than SOME means greater than at least one value that is greater than the minimum.

SELECT * FROM PermanentEmployee

WHERE Age > SOME (SELECT Age FROM ContractEmployee)

The above query will produce the same result set as shown below.

SOME Operator in SQL Server

We got the same records what were returned by our first query. If you will use =SOME that is equal to IN. With SOME you can use different comparison operators such as = , <> , != , > , >= , !> , < , <= , !< 

In the next article I will discuss EXISTS Operator in SQL Server.

SUMMARY

In this article, I try to explain the SOME Operator in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *