Question 112896 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – When to use ALL in where and when not to use ALL in where clause?

enter image description here

WITH RECURSIVE CTE AS
    (SELECT MIN(period_start) as date
     FROM Sales 
     UNION ALL
     SELECT DATE_ADD(date, INTERVAL 1 day)
     FROM CTE
     WHERE date <= ALL (SELECT MAX(period_end) FROM Sales))

What is the difference of using ALL in the where clause and not using ALL?

I have tried both, all returns the same result.

Tags:

2

Answers


  1. 0

    The ALL function means the comparison must be true for all values returned by the subquery. But the subquery returns only one value, because you used the aggregate function MAX() with no GROUP BY clause.

    Using ALL in this specific example makes no difference. It does no harm, but it is superfluous.

    Login or Signup to reply.
  2. 0
    1. ANY and ALL operators are used with WHERE or HAVING.
    2. ANY and ALL operate on subqueries that return multiple values.
    3. ANY returns true if any of the subquery values meet the condition.
    4. ALL returns true if all of the subquery values meet the condition.

    Example:

    SELECT column-names
FROM table-name
WHERE column-name operator ALL 
      (SELECT column-name
       FROM table-name
       WHERE condition)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search