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.

CodePudding user response：

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.

CodePudding user response：

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)