Sql ALL Operator


The SQL ALL operator are used on subqueries that return multiple values.

The SQL ALL operator returns true if all of the subquery values (single value or from list of values) meet the condition on outer table query.

It can be used on any valid sql select statement with where and having clause.


Sql ALL Syntax

The below syntax is used to create all operator with sub query or inner query.


SELECT column-name1, column-name2, ... column-nameN 
FROM table-name 
WHERE column-name operator ALL 
(SELECT column-name FROM table-name WHERE condition)

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

Sample Database Table - EmpInfo

ID EmpName Gender Age City
1 Harish Karthik Male 31 Hyderbhad
2 Nirmala Female 21 Delhi
3 Keshavan Male 21 Pune
4 Ranjani Mai Female 33 Mysore
5 Geetha Female 30 Chennai
6 Chandra Female 29 Madurai

SQL ALL Operator Example With Numeric Field

The following SQL statement will display records from "EmpInfo" table and It compares a value from main table to each value in a list or results from a subquery and evaluates to true if all of the subquery values meet the condition.


SELECT * FROM EmpInfo 
WHERE Age > ALL (SELECT Age FROM EmpInfo WHERE ID > 4)

The sub-query returns 5 and 6. i.e, These are the ages (30, 29) for which employee "ID" is greater than "4".

Then main query is compared with this, if any of the ages in table is greater than all of the values of sub-query (i.e the ages in table should be greater than both 30 and 29) .

The result of above query is:

ID EmpName Gender Age City
1 Harish Karthik Male 31 Hyderbhad
4 Ranjani Mai Female 33 Mysore

SQL ALL Operator Example With Character Field

The sql 'all' operator will work on a single value (string) and it will not work if the subquery contains multiple values (only unique values). See below statement,


// ERROR STATEMENT

SELECT * FROM EmpInfo 
WHERE Gender = ALL (SELECT Gender FROM EmpInfo)

The sub query will return multiple string values which are "Male" and "Female". So the above query will not return any result and it will display a error message.


SELECT * FROM EmpInfo 
WHERE ID > 4 AND Gender = ALL (SELECT Gender FROM EmpInfo WHERE City = 'Chennai')

The sub-query returns "Female". for which employee "City" is equal to "Chennai".

Then main query is compared with this, if any of the gender in table is equal to all of the values of sub-query.

The result of above query is:

ID EmpName Gender Age City
5 Geetha Female 30 Chennai
6 Chandra Female 29 Madurai