Subquery using UNION ALL in MySQL

Question

I guess I don't understand the order in which subqueries work.

When I run this SQL statement I get 1 (out of 3 that exist in the table) random '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;

One random id is returned which is what I want.

BUT when I wrap the previous SQL statement within another SQL statement to get the friend's name and id (from the id in sub-query) the results come back randomly as either empty or 1 friend or 2 friends or all 3 friends :

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);

I want it to emit the same behaviour as the first code snippet.

score 0 · Accepted Answer · answered Oct 15 '20 at 02:25

The problem is that the subquery is being re-executed for every row being tested in profile. Each time it returns a different random ID; if that ID happens to match the current row of profile, the row is returned.

Instead of using WHERE id =, use a JOIN. This will just run the subquery once.

SELECT p.id, p.name
FROM profile AS p
JOIN (
    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) AS r
ON p.id = r.Connections

score 0 · Answer 2 · answered Oct 17 '20 at 01:27

Are you really asking for all the sender_ids and one receiver_id?

It feels like you have an extra layer of SELECTs.

When in doubt, add extra parentheses:

( SELECT ... ) 
UNION ALL
( SELECT ... ORDER BY ...)   -- this ORDER BY applies only to the select
ORDER BY ...    -- This applies to the result of the UNION, not the second select

Subquery using UNION ALL in MySQL

2 Answers2