Features Added in MySQL 8.0 •Beginning with MySQL 8.0.21, a single-table  UPDATE  or  DELETE  statement can now in many cases make use of a semijoin transformation or subquery materialization. This applies to statements of the forms shown here: • UPDATE t1 SET t1.a= value  WHERE t1.a IN (SELECT t2.a FROM t2) • DELETE FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2) •The  UPDATE  or  DELETE  statement uses a subquery having a  [NOT] IN  or  [NOT] EXISTS predicate. •The statement has no  ORDER BY  clause, and has no  LIMIT  clause. •The target table does not support read-before-write removal (relevant only for  NDB  tables). •Semijoin or subquery materialization is allowed, based on any hints contained in the subquery and the value of  optimizer_switch . • Improved hash join performance.   MySQL 8.0.23 reimplements the hash table used for hash joins, resulting in several improvements in hash join performance. This work includes a fix for an issue (Bug #31516149, Bug #99933) whereby only roughly 2/3 of the memory allocated for the join buffer ( join_buffer_size ) could actually be used by a hash join. • Common table expressions.   MySQL now supports common table expressions, both nonrecursive and recursive. Common table expressions enable use of named temporary result sets, implemented by permitting a  WITH  clause preceding  SELECT  statements and certain other statements. For more information, see Section 13.2.15, “WITH (Common Table Expressions)” . • Window functions.   MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row. These include functions such as  RANK() ,  LAG() , and NTILE() . In addition, several existing aggregate functions now can be used as window functions (for example,  SUM()  and  AVG() ). For more information, see Section 12.21, “Window Functions” . • Lateral derived tables.   A derived table now may be preceded by the  LATERAL  keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same  FROM  clause. Lateral 27 As of MySQL 8.0.19, the recursive  SELECT  part of a recursive common table expression (CTE) supports a  LIMIT  clause.  LIMIT  with  OFFSET  is also supported. See Recursive Common Table Expressions, for more information. The new hash table is generally faster than the old one, and uses less memory for alignment, keys/ values, and in scenarios where there are many equal keys. In addition, the server can now free old memory when the size of the hash table increases. Alo beginning with MySQL 8.0.21, semi-consistent reads are supported by multi-table  UPDATE statements using  InnoDB  tables, for transaction isolation levels weaker than  REPEATABLE READ . When the semijoin optimization is used for an eligible single-table  DELETE  or  UPDATE , this is visible in the optimizer trace: for a multi-table statement there is a  join_optimization  object in the trace, while there is none for a single-table statement. The conversion is also visible in the output of  EXPLAIN FORMAT=TREE  or  EXPLAIN ANALYZE ; a single-table statement shows  <not executable by iterator executor> , while a multi-table statement reports a full plan. This can be done for a single-table  UPDATE  or  DELETE  meeting the following conditions: (The multi-table versions of  UPDATE  and  DELETE  do not support  ORDER BY  or  LIMIT .)