SOME Operator in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

SOME Operator in Oracle with Examples

In this article, I am going to discuss SOME Operator in Oracle with Examples. Please read our previous article where we discussed ALL Operator in Oracle with examples. At the end of this article, you will understand what is SOME Operator and when and how to use SOME Operator in Oracle with Examples.

What is SOME Operator in Oracle?

The SOME Operator in Oracle 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. The SOME Operator must match at least one row in the subquery and must be preceded by comparison operators. Suppose using greater than (>) with SOME Operator 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 examples.

Syntax to use SOME Operator in Oracle:

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 <=).

Understanding SOME Operator in Oracle with Examples:

Let us understand the SOME Operator in Oracle with examples. We are going to use the following PermanentEmployee and ContractEmployee tables to understand Some Operator.

Please use the following SQL Script to create and populate the PermanentEmployee and ContractEmployee tables with the required data.

CREATE TABLE PermanentEmployee
(
  EmployeeId INT,
  Name VARCHAR(15),
  Gender VARCHAR(10),
  Department varchar(10),
  Age INT
);

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

CREATE TABLE ContractEmployee
(
  EmployeeId INT,
  Name VARCHAR(15),
  Gender VARCHAR(10),
  Department varchar(10),
  Age INT
);

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

SOME Operator Example:

Here, we have two tables i.e. PermanentEmployee and ContractEmployee. Both the tables have the Age column. If you want to fetch 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 > (SELECT MIN(Age) FROM ContractEmployee);

Once you execute the above query, you will get the following output.

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

SELECT * FROM PermanentEmployee WHERE Age > SOME (SELECT Age FROM ContractEmployee);

When you execute the above query, you will get the following output.

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 Oracle with Examples. Here, in this article, I try to explain the SOME Operator in Oracle 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 Oracle with Examples article.