Re: A odd case about mysql sub query

在 2016/5/6 15:45, haiwen zhu 写道:

Hi guys, I have a table like this: +----+-------+-------+ | id | col_a | col_b | +----+-------+-------+

|  1 | a     | b     |
|  2 | a     | b     |
|  3 | a     | c     |
|  4 | a     | c     |

+----+-------+-------+ execute sql a:

select * from test where id not in (select id from (select id from test group by col_a,col_b) as a);

can get result +----+-------+-------+ | id | col_a | col_b | +----+-------+-------+

|  2 | a     | b     |
|  4 | a     | c     |

+----+-------+-------+ while execute sql b:

select * from test where id not in (select id from (select * from test) as a group by col_a,col_a);

get empty result. I'm very confused with this, somebody has idea about why? I tried in 10.1.13-MariaDB and mysql5.6.

in mysql 5.5.46, the second result I got was : +----+-------+-------+ | id | col_a | col_b | +----+-------+-------+

|  2 | a     | b     |
|  3 | a     | c     |
|  4 | a     | c     |

+----+-------+-------+ (:(