How to SELECT a random row in a MySQL Database?

Question

I am trying to select a random row from a column in my database.

I need the query to search/make sure that the random row selected is not equal to any of the values stored in two other columns.

Example:

DATABASE NAME: database_name

TABLE1 contains one column: male_id

TABLE2 contains two columns: number_list1 and number_list2

I need a query that will find a random number in the male_id column THAT IS NOT FOUND IN number_list1 or number_list2. Is there anyway to do this? Here was my best shot at it (causes an error):

SELECT male_id FROM TABLE1 WHERE male_id IS NOT (SELECT number_list1 FROM TABLE2) 
AND (SELECT number_list2 FROM TABLE2) ORDER BY RAND()

your order by rand() works, but it's not efficient. https://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast. Your error is the the where clause (has nothing to do with rand() ) "male_id is not () and () " is not correct syntax. — Twelfth, Mar 16 '18 at 18:49
Possible duplicate of [How to request a random row in SQL?](https://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) — cacti5, Mar 16 '18 at 18:55

score 0 · Answer 1 · answered Mar 16 '18 at 18:49

0

Something like this should work.

In general, you REALLY want to avoid not in with sub query in mySQL

SELECT male_id FROM TABLE1 t1 
  LEFT JOIN TABLE2 t2 on t1.male_id = t2.number_list1
  LEFT JOIN TABLE2 t3 on t1.male_id = t3.number_list2
where t2.number_list1 is null and t3.number_list2 is null
 ORDER BY RAND();

answered Mar 16 '18 at 18:49

Brian Hoover

7,861
2
28
41

There is no reason to avoid subqueries as a general rule. Sometimes they are bad. Sometimes they are good. – Gordon Linoff Mar 16 '18 at 19:07
@GordonLinoff - NOT IN? I'm curious, when would NOT IN with a subquery be a good thing? – Brian Hoover Mar 16 '18 at 19:45
. . I wouldn't use `NOT IN` with a subquery in general. – Gordon Linoff Mar 17 '18 at 02:58

score 0 · Answer 2 · answered Mar 16 '18 at 19:05

You are looking for NOT IN (or NOT EXISTS). Assuming none of the values in the subquery are NULL, you can do:

SELECT t1.male_id
FROM TABLE1 t1
WHERE t1.male_id NOT IN (SELECT t2.number_list1 FROM TABLE2 t2) AND
      t1.male_id NOT IN (SELECT t2.number_list2 FROM TABLE2 t2)
ORDER BY RAND()
LIMIT 1;

For various reasons, I prefer NOT EXISTS:

SELECT t1.male_id
FROM TABLE1 t1
WHERE NOT EXISTS (SELECT 1 FROM TABLE2 t2 WHERE t2.number_list1 = t1.male_id) AND
      NOT EXISTS (SELECT 1 FROM TABLE2 t2 WHERE t2.number_list2 = t1.male_id)
ORDER BY RAND()
LIMIT 1;

For performance, you want indexes on TABLE2(number_list1) and TABLE2(number_list2).

score 0 · Answer 3 · answered Mar 16 '18 at 19:07

0

Try this:

SELECT male_id 
FROM TABLE1 A
WHERE NOT EXISTS
(SELECT NULL 
   FROM TABLE2 B
   WHERE B.number_list1=A.male_id
   OR B.number_list2=A.male_id)   
ORDER BY RAND()
LIMIT 1;

answered Mar 16 '18 at 19:07

cdaiga

4,861
3
22
42

How to SELECT a random row in a MySQL Database?

3 Answers3