MySQL ALL with Example

In SQL language, ALL statement is used to compare a value in the set of values in a subquery. In other words, this command ensures that a condition is “equal”, “different”, “greater than”, “less than”, “greater than or equal to” or “less than or equal to” for all results returned by a subquery.
 

Syntax :

This command is used in a conditional clause between the condition operator and the subquery. The example below shows a basic example:

SELECT *
FROM table1
WHERE condition > ALL (
    SELECT *
    FROM table2
    WHERE condition2
)

Note: conditional operators can be the following: =, <, >, <>, !=, <=, >=, !> or !<.
 

 

Example :

Let’s consider a query similar to the basic syntax presented above:

SELECT column1
FROM table1
WHERE column1 > ALL (
    SELECT column1
    FROM table2
)

With this query, if we suppose that in table1 there is a result with the value 10, here are the different results of the condition depending on the content of table2 :

• The condition is true (cf. TRUE) if table2 contains {-5,0,+5} because all values are less than 10
• The condition is false (cf. FALSE) if table2 contains {12,6,NULL,-100} because at least one value is less than 10
• The condition is unknown (cf. UNKNOW) if table2 is empty

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More