MariaDB Tutorial MariaDB Advanced MariaDB Database Account Management MariaDB References

MariaDB - ALL Operator



The MariaDB ALL Operator is used with WHERE and HAVING clause. It returns TRUE if all of the subquery values satisfies the condition.

Syntax

The syntax for using ALL operator in MariaDB is given below:

SELECT column1, column2, column3, ...
FROM table_name
WHERE column_name operator ALL
(SELECT column1, column2, ... 
FROM table_name WHERE condition);

Note: The operator mentioned above must be a standard comparison operator (=, <>, !=, <, <=, > or >=).

Example:

Consider a database containing tables called Employee and Bonus_Paid with the following records:

Table 1: Employee table

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

Table 2: Bonus_Paid table

EmpIDBonus
1500
2400
3450
4550
5400
6600

  • To select all employees from Employee table whose bonus is less than 500, the query is given below.

    SELECT * FROM Employee
    WHERE EmpID <> ALL
    (SELECT EmpID 
    FROM Bonus_Paid WHERE Bonus >= 500);
    

    The subquery will produce following result:

    EmpID
    1
    4
    6

    The query then compares EmpID values with all subquery values and fetches only those results which return true with all subquery values. It will produce the following result:

    EmpIDNameCityAgeSalary
    2MarryLondon242750
    3JoLondon272800
    5RameshNew Delhi283000

❮ MariaDB - Operators