SQL Tutorial SQL Advanced SQL Database SQL References

SQL - SOME Operator



The SQL SOME Operator is used with WHERE and HAVING clause. It returns TRUE if any of the subquery values satisfies the condition.

Syntax

The syntax for using SOME operator is given below:

SELECT column1, column2, column3, ...
FROM table_name
WHERE column_name operator SOME
(SELECT column1, column2, ... 
FROM table_name WHERE condition);

Note: The operator mentioned above must be a standard comparison operator (=, <>, !=, <, <=, > or >=).

Example:

Consider a database containing tables called Employee and Contact_Info with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Contact_Info table

Phone_NumberEmpIDAddressGender
+1-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

  • To select records of male employees from Employee table, the SQL code is given below.

    SELECT * FROM Employee
    WHERE EmpID = SOME
    (SELECT EmpID 
    FROM Contact_Info WHERE Gender = 'M');
    

    The subquery will produce following result:

    EmpID
    3
    6
    7
    8

    The SQL query then compares EmpID values with subquery result. It returns TRUE as few records satisfy the condition and produces the following result:

    EmpIDNameCityAgeSalary
    3JoParis272800
    6HuangBeijing282800

❮ SQL - Operators