MySQL Subquery Errors and Limits: Error 1242, 1093

Subqueries have a few special rules. Most surprises come from the same handful of causes.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    added_on DATE NOT NULL
);

INSERT INTO products (name, category, price, stock, added_on) VALUES
    ('Notebook',      'stationery',  80.00, 120, '2026-01-10'),
    ('Gel Pen',       'stationery',  15.50, 300, '2026-01-12'),
    ('Backpack',      'bags',       999.00,  25, '2026-02-01'),
    ('Marker Set',    'stationery', 145.00,   0, '2026-02-15'),
    ('Desk Lamp',     'home',       650.00,  40, '2026-03-03'),
    ('Water Bottle',  'home',       220.00,  75, '2026-03-20'),
    ('Laptop Sleeve', 'bags',       450.00,  18, '2026-04-02'),
    ('Sticky Notes',  'stationery',  35.00, 210, '2026-04-18');

1. “Subquery returns more than 1 row” (error 1242)

query.sql

SQL

SELECT name FROM products WHERE price = (SELECT price FROM products WHERE category = 'home');

Output

OUTPUT

ERROR 1242 (21000): Subquery returns more than 1 row

A scalar context needs one value. Use IN, or an aggregate, or a more specific condition:

query.sql

SQL

1
2

SELECT name FROM products WHERE price IN (SELECT price FROM products WHERE category = 'home') ORDER BY name;
SELECT name FROM products WHERE price = (SELECT MAX(price) FROM products WHERE category = 'home');

Output

OUTPUT

+--------------+
| name         |
+--------------+
| Desk Lamp    |
| Water Bottle |
+--------------+
2 rows in set

+-----------+
| name      |
+-----------+
| Desk Lamp |
+-----------+
1 row in set

2. LIMIT inside IN (error 1235)

query.sql

SQL

SELECT name FROM products WHERE id IN (SELECT id FROM products ORDER BY price DESC LIMIT 3);

Output

OUTPUT

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

MySQL does not support LIMIT in a subquery used with IN, ALL, ANY or SOME. The workaround is to put the limited query in a derived table and join it, or to use IN with a derived table:

query.sql

SQL

SELECT p.name FROM products p JOIN (SELECT id FROM products ORDER BY price DESC LIMIT 3) AS top_three ON top_three.id = p.id ORDER BY p.price DESC;

Output

OUTPUT

+---------------+
| name          |
+---------------+
| Backpack      |
| Desk Lamp     |
| Laptop Sleeve |
+---------------+
3 rows in set

3. Cannot select from the table you are changing (error 1093)

query.sql

SQL

DELETE FROM products WHERE price < (SELECT AVG(price) FROM products);

Output

OUTPUT

ERROR 1093 (HY000): You can't specify target table 'products' for update in FROM clause

An UPDATE or DELETE cannot read the same table in a subquery. Wrap the subquery in a derived table, so that MySQL materialises it first:

query.sql

SQL

1
2

DELETE FROM products WHERE price < (SELECT avg_price FROM (SELECT AVG(price) AS avg_price FROM products) AS t);
SELECT COUNT(*) AS products_left FROM products;

Output

OUTPUT

+---------------+
| products_left |
+---------------+
|             3 |
+---------------+
1 row in set

4. NOT IN and NULL

query.sql

SQL

1
2

SELECT COUNT(*) AS with_null_in_list FROM products WHERE id NOT IN (SELECT NULL UNION ALL SELECT 1);
SELECT COUNT(*) AS using_not_exists FROM products p WHERE NOT EXISTS (SELECT 1 WHERE p.id = 1);

Output

OUTPUT

+-------------------+
| with_null_in_list |
+-------------------+
|                 0 |
+-------------------+
1 row in set

+------------------+
| using_not_exists |
+------------------+
|                3 |
+------------------+
1 row in set

A NULL in the list makes NOT IN return nothing. NOT EXISTS is safe (IN).

5. Column count mismatch

query.sql

SQL

SELECT name FROM products WHERE (category, price) IN (SELECT category FROM products);

Output

OUTPUT

ERROR 1241 (21000): Operand should contain 2 column(s)

A row comparison needs the same number of columns on both sides.

6. Ambiguous column names inside subqueries

When the inner and outer queries use the same table, an unqualified column refers to the innermost table that has it:

query.sql

SQL

SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products WHERE category = category) ORDER BY id;

Output

OUTPUT

+----------+
| name     |
+----------+
| Backpack |
+----------+
1 row in set

category = category inside the subquery compares the inner column with itself. Use aliases (p and x).

7. Performance traps

A correlated scalar subquery in the SELECT list on a large table runs once per row.
IN (subquery) where the subquery returns millions of rows and has no index.
A subquery in ORDER BY.
A subquery in the WHERE of a very frequently executed statement, without an index on its filter columns.

Use EXPLAIN. Rows with DEPENDENT SUBQUERY and a large number of rows are a warning sign (EXPLAIN).

8. ORDER BY in subqueries is ignored

In a derived table or IN subquery without LIMIT, an ORDER BY has no effect on the outer result. SQL does not promise any order for a subquery:

query.sql

SQL

SELECT name FROM (SELECT name FROM products ORDER BY price DESC) AS t;

The order of the outer rows is not guaranteed. Put the ORDER BY in the outer query.

9. Too many levels

Three levels of nesting are hard to read and debug. Use CTEs:

query.sql

SQL

1
2

WITH avg_per_category AS (SELECT category, AVG(price) AS avg_price FROM products GROUP BY category)
SELECT p.name, p.category, p.price FROM products p JOIN avg_per_category a ON a.category = p.category WHERE p.price > a.avg_price ORDER BY p.category, p.price;

Output

OUTPUT

+----------+----------+--------+
| name     | category | price  |
+----------+----------+--------+
| Backpack | bags     | 999.00 |
+----------+----------+--------+
1 row in set

A debugging checklist

Run the inner query alone. How many rows and columns does it return?
Replace the subquery by a literal result and run the outer query.
Check the aliases: is every column prefixed?
Check for NULLs in the subquery’s result.
Look at EXPLAIN.

lightbulb

Prefer clarity

When a subquery stops being obvious, turn it into a CTE with a good name. Readers and future you will be grateful.