Sql ALL Operator

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)

Sample Database Table - EmpInfo

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:

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: