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.
Search Keys
- t-sql all operator
- sql union all operator
- ms sql all operator
- linq to sql all operator
- sql server script all operators
- sql in operator all values
- sql logical operators all
- sql union all example
- sql union all
- sql union all order by
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 |