ALL

Example

SELECT Employee_Name
FROM Employees
WHERE Salary > ALL (SELECT Salary
                    FROM Employees
                    WHERE Department_ID = 2);

Output

+---------------+
| Employee_Name |
+---------------+
|      John     |
|      Jane     |
+---------------+

Explanation

In the above example, the SQL statement is selecting all Employee_Name from the Employees table where the Salary is greater than all the salaries of employees present in Department_ID 2. As a result, we get the names of the employees who are earning more than every employee from Department_ID 2, which in this case, are John and Jane.

Example

CREATE TABLE test(
   id INT PRIMARY KEY NOT NULL,
   salary FLOAT NOT NULL
);

INSERT INTO test(id, salary)
VALUES (1, 10000),(2, 20000),(3, 30000),(4, 40000),(5, 50000);

SELECT salary > ALL (SELECT salary FROM test WHERE salary < 50000)
FROM test
WHERE id = 5;

Output

Explanation

The ALL operator returns true if all the comparisons are true when used with a subquery. In this example, the subquery selects all salaries from the test table which are less than 50000, and then checks if the salary in the test table where the id is 5 is greater than all the salaries returned from the subquery. The output ‘t’ in PostgreSQL is equivalent to ‘true’.

Example

SELECT Employee_ID
FROM Employees
WHERE Salary > ALL
(SELECT Salary
 FROM Employees
 WHERE Department_ID = 3);

Output

Employee_ID
-----------
5
8

Explanation

The SQL query selects all employees whose salary is higher than all employees in Department 3. The result set consists of the IDs of these employees.

Example

SELECT department_id, first_name, last_name, salary
FROM employees
WHERE salary > ALL
  (SELECT salary
  FROM employees
  WHERE department_id = 30);

Output

DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- ---------- --------- ------
20            Sam        Fisher    10000
10            John       Doe       12000

Explanation

In the given SQL query, the “ALL” operator is used with a subquery in the WHERE clause. The subquery returns the salaries of all employees in the department with id 30. The outer query then compares the salary of each employee in the ‘employees’ table with the set of salaries retrieved by the subquery. If an employee’s salary is greater than all of the salaries fetched by the subquery, their details are included in the result set. In this example, the result set consists of employees from departments other than 30, who earn more than every employee in department 30.

Example

CREATE TABLE Employee(ID int, Name varchar(255), Salary int);
INSERT INTO Employee (ID, Name, Salary)
VALUES (1, 'John', 50000), (2, 'Emma', 65000), (3, 'Micheal', 75000);
SELECT Salary FROM Employee WHERE Salary >= ALL
(SELECT Salary FROM Employee WHERE Name != 'John');

Output

Explanation

The given SQL code first creates a table named ‘Employee’ with columns ‘ID’, ‘Name’, and ‘Salary’. It then inserts three rows into this table. The SELECT statement then retrieves all salaries from the Employee that are greater than or equal to all the salaries of the Employees who are not named ‘John’. In this case, only John’s salary of 50000 is returned because it is the smallest salary.