How to fix inconsistent mysql results?-CodePudding

My MySQL query

(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 1) 
UNION 
(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 1) 
UNION 
(SELECT * FROM names  WHERE Rate = 35 ORDER BY RAND() LIMIT 1);

it should give me something like this

 ---- ----- ------ 
| id | Rate| num  |
 ---- ----- ------ 
| id |  75 |  987 |
| id |  75 |  987 |
| id |  35 |  987 |
 - -- ----- ------

but sometimes it only gives me 2 rows, something like this

 ---- ----- ------ 
| id | Rate| num  |
 ---- ----- ------ 
| id |  75 |  987 |
| id |  35 |  987 |
 - -- ----- ------

CodePudding user response：

Note the difference between UNION and UNION ALL

UNION will remove duplicate rows
UNION ALL keeps all rows

CodePudding user response：

UNION removes duplicate rows.

The first 2 queries may return the same row and in this case one of them will only be in the results.

If you don't mind duplicate rows in the results then change to UNION ALL:

(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 1) 
UNION ALL 
(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 1) 
UNION ALL
(SELECT * FROM names WHERE Rate = 35 ORDER BY RAND() LIMIT 1);

If you want unique rows then unify the first 2 queries to only 1 with LIMIT 2 so that they return 2 rows:

(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 2) 
UNION ALL 
(SELECT * FROM names WHERE Rate = 35 ORDER BY RAND() LIMIT 1);

I use here UNION ALL because it performs better than UNION.

CodePudding user response：

To get 3 different rows from the table, do simply:

SELECT * FROM names WHERE Rate = 35 ORDER BY RAND() LIMIT 3;