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

PlayerNameDepartmentScores
JasonIT3000
IreneIT1500
JaneMarketing1000
DavidMarketing2500
PaulHR2000
JamesHR2000

SQL statement :

SELECT * FROM GameScores
WHERE Scores > ALL (SELECT MAX(Scores) as Scores FROM GameScores WHERE Department ='Marketing')

Result:

PlayerNameDepartmentScores
JasonIT3000