SQL Error #1242: Subquery Returns More Than 1 Row - Explained and Solved

This error occurs when a subquery used within a query returns more than one row. In SQL, a subquery is a query nested inside another query.

For example, imagine you have two tables: 'customers' and 'orders'. You want to find all customers who have placed more than one order. You might write the following query:

SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 1);

In this query, the subquery is 'SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 1'. It's designed to return the 'customer_id' of all customers with multiple orders. However, if two or more customers have made more than one order, this subquery will return multiple rows, causing the error 'Subquery returns more than 1 row'.

To fix this, you can modify the subquery to return only one row. One method is to use an aggregate function like 'MAX()' or 'MIN()' to select a single value from the subquery's result set. For instance:

SELECT * FROM customers WHERE customer_id IN (SELECT MAX(customer_id) FROM orders GROUP BY customer_id HAVING COUNT(*) > 1);

In this revised query, the subquery now returns only one 'customer_id' for each group of customers with multiple orders, thanks to the 'MAX()' function. This guarantees that the subquery returns a single row for each customer, preventing the error.