SOME Operator in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

SOME Operator in SQL Server with Examples

In this article, I am going to discuss SOME Operator in SQL Server with Examples. Please read our previous article where we discussed ANY Operator with examples. At the end of this article, you will understand how, when, and where you can use this special operator in SQL Server.

What is SOME Operator in SQL Server?

The SOME Operator in SQL Server is used to 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. If this is not clear at the moment, then don’t worry we will try to understand this with some examples.

Understanding SQL Server SOME Operator with Examples:

Let us understand the SQL Server SOME Operator with some examples. We are going to use the following PermanentEmployee and ContractEmployee tables to understand SOME Operator.

Please use the below SQL Script to create the database EmployeeDB, create PermanentEmployee and ContractEmployee tables and populate these two tables with the required data.

-- Create a database
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

ContractEmployee Table with data:

-- 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

Syntax to use SOME Operator in SQL Server:

Parameters:

1. column_name: Name of the column of the table.
2. 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.
3. table_name: Name of the table.
4. 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.
5. comparison_operator: Compares the expression to the subquery. The comparison must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).

Let’s understand SOME Operator with examples.

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 the PermanentEmployee table where the Age is at-least greater than one value from the Age column of the ContractEmployee table. What would be your query? You can use the subquery and MIN function to write your query for the above requirement as shown below.

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

The above query will produce the below result.

Use SOME Operator to get the required results.

You can also use the SOME operator instead of using the 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.

We got the same records that 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 am going to discuss EXISTS Operator in SQL Server. Here, in this article, I try to explain the SOME Operator in SQL Server with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this SOME Operator in SQL Server with Examples article.