SQL - SOME Keyword
The SQL SOME keyword 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 keyword 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:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
Table 2: Contact_Info table
Phone_Number | EmpID | Address | Gender |
---|---|---|---|
+1-8054098000 | 2 | Brooklyn, New York, USA | F |
+33-147996101 | 3 | Grenelle, Paris, France | M |
+31-201150319 | 4 | Geuzenveld, Amsterdam, Netherlands | F |
+86-1099732458 | 6 | Yizhuangzhen, Beijing, China | M |
+65-67234824 | 7 | Yishun, Singapore | M |
+81-357799072 | 8 | Koto City, Tokyo, Japan | M |
-
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:
EmpID Name City Age Salary 3 Jo Paris 27 2800 6 Huang Beijing 28 2800
❮ SQL Keywords