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

Question

I'm learning SQL, so I test some queries:

mysql> select * from users where id = 1 or (select id from users);

result:

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

I found that if I use any concat functions like group_concat, the error will disappear.

But I need to know why the error happened and I'm just select 1 column ' id ' in the subquery.

select * from users where id = 1 or (select id from users limit 1); — Ed Bangga, Nov 06 '19 at 03:37

score 0 · Accepted Answer · answered Nov 06 '19 at 04:05

in your example in (select id from users) result will be

|----|
| id |
|----|
|  1 |
|  2 |
| .. |
|  n |
|----|

Error says:

Subquery returns more than 1 row

as you can see what I mentioned, your subquery returns a lot of rows.

As I see in your example. You want to load full user data if it exists, else load id from the first user. Why do not do something like

select * from users where id = 1 or not id = 1;

1 Answers1