SQL ALL operator is used to compare a value to all values in another value set or subquery return.
However, the selected list in SQL ALL subquery is not used to execute the outer query and it will list out all the records of table selected as long as subquery return at least a single record.
The SQL syntax for the ALL operator is:
SELECT [COLUMN NAME] FROM [TABLE NAME]
WHERE [CONDITION 1] ALL (
SELECT [COLUMN NAME] FROM [TABLE NAME]
WHERE [CONDITION 2])
EXAMPLE :
Table GameScores
PlayerName | Department | Scores |
Jason | IT | 3000 |
Irene | IT | 1500 |
Jane | Marketing | 1000 |
David | Marketing | 2500 |
Paul | HR | 2000 |
James | HR | 2000 |
SQL statement :
SELECT * FROM GameScores
WHERE Scores > ALL (SELECT MAX(Scores) as Scores FROM GameScores WHERE Department ='Marketing')
Result:
PlayerName | Department | Scores |
Jason | IT | 3000 |