Subquery Kinds

A column subquery returns one column and feeds IN, ANY and ALL (IN, ANY, ALL, and EXISTS). A row subquery returns one row, compared with a row constructor: (customer_id, status) = (SELECT customer_id, status FROM orders WHERE id = 1) finds Ana's shipped orders 1 and 4. A table subquery returns rows and columns, and a tuple IN tests whole rows:

SELECT id, product_id, rating FROM reviews
WHERE product_id IN (1, 2) AND (product_id, rating) IN
      (SELECT product_id, MAX(rating) FROM reviews GROUP BY product_id);
SELECT sku FROM products
WHERE id IN (SELECT product_id FROM order_items ORDER BY quantity DESC LIMIT 2);

+----+------------+--------+
| id | product_id | rating |
+----+------------+--------+
|  1 |          1 |      5 |
|  2 |          2 |      4 |
+----+------------+--------+
2 rows in set (0.001 sec)
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME
  subquery'

This greatest-per-group query drops Elif's 4 for product 1 (review 6) and would keep ties. Rows compare left to right, and (1, 2) = (1, NULL) is unknown. For the refused LIMIT, wrap the query in a derived table, IN (SELECT product_id FROM (SELECT ... LIMIT 2) AS t), which returns the mug and the sticker pack. Since 8.0.19, IN (TABLE wishlist) also works.