mysql union 子查询_MySQL中使用UNION ALL的子查询

什么是有效的-当我运行这个SQL语句时,我得到1个(表中存在的3个)随机的“friend id”:

SELECT t1.sender_id AS Connections FROM

(SELECT DISTINCT sender_id

FROM connection

WHERE receiver_id = 'my_id'

AND status = 'Approved') t1

UNION ALL

SELECT t2.receiver_id FROM

(SELECT DISTINCT receiver_id

FROM connection

WHERE sender_id = 'my_id'

AND status = 'Approved') t2

ORDER BY RAND() LIMIT 1;

什么不起作用-当我将之前的SQL语句包装在另一个SQL语句中以获取朋友的名称和id(从子查询中的id)时,结果将随机返回为空或1个朋友或2个朋友或全部3个朋友:

SELECT id, name FROM profile

WHERE id = (

SELECT t1.sender_id AS Connections FROM

(SELECT DISTINCT sender_id

FROM connection

WHERE receiver_id = 'my_id'

AND status = 'Approved') t1

UNION ALL

SELECT t2.receiver_id FROM

(SELECT DISTINCT receiver_id

FROM connection

WHERE sender_id = 'my_id'

AND status = 'Approved') t2

ORDER BY RAND() LIMIT 1);

这将返回一个如下所示的空集:

empty set

我想要的是什么(或不想要的是什么):

single set

或者三排:

three rows