Generate a random value that does not exist in another table MySQL

Question

I have a problem in my SQL syntax. I want to add a random phone number between 10000 and 99999. I have this query so far, but I'm failing to run it in the PHPMyAdmin:

SELECT round(RAND()*99999) AS `NUM` 
WHERE `NUM` NOT IN (SELECT `PHONE_NUMBER` FROM `PHONE`);

This is the error:

1064 - Something is wrong in its syntax near 'WHERE NUM NOT IN (SELECT PHONE_NUMBER FROM PHONE)' on line 1

You simply cannot do something like this with straight SQL. You will need to use a store procedure, and SELECT in a loop, while the number doesn't exist in that table. — Blue, Nov 04 '18 at 20:49

score 7 · Answer 1 · answered Nov 04 '18 at 22:17

7

You can do:

SELECT NUM
FROM (SELECT round(RAND()*99999) AS `NUM`) x 
WHERE `NUM` NOT IN (SELECT `PHONE_NUMBER` FROM `PHONE`);

NUM is not known in the WHERE clause. And, you can't have a WHERE without a FROM.

answered Nov 04 '18 at 22:17

Gordon Linoff

1,242,037
58
646
786

Nick · Answer 2 · 2018-11-05T06:52:15.917

An issue with @GordonLinoff answer is that it may possibly return nothing rather than a number that doesn't exist in the other table. There are a couple of ways around this: use a stored function, or use a numbers table. This is how to do it with a stored function:

DELIMITER //
DROP FUNCTION IF EXISTS get_random_phone //
CREATE FUNCTION get_random_phone(min INT, max INT) RETURNS INT
NOT DETERMINISTIC
BEGIN
  DECLARE num INT;
  DECLARE finished INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
  REPEAT
    SET num = min + FLOOR(RAND() * (max - min + 1));
    SELECT phone_number INTO num FROM phone WHERE phone_number = num;
  UNTIL finished = 1
  END REPEAT;
  RETURN num;
END //

Then your query simply becomes

SELECT get_random_phone(10000, 99999)

Alternatively you can create a numbers table and LEFT JOIN that to your phone number table, selecting only rows with no match and then ordering by RAND() and using LIMIT 1 to return only one result. Note though that this can be quite slow without indexes on num and phone_number.

CREATE TABLE numbers (num INT PRIMARY KEY) AS
SELECT n1.n + n10.n*10 + n100.n*100 + n1000.n*1000 + n10000.n*10000 AS num FROM
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n1
CROSS JOIN
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n10
CROSS JOIN
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n100
CROSS JOIN
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n1000
CROSS JOIN
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n10000
ORDER BY NUM

In this case your query would be:

SELECT num
FROM numbers
LEFT JOIN phone ON phone.phone_number = numbers.num
WHERE phone.phone_number IS NULL AND num BETWEEN 10000 AND 99999
ORDER BY RAND()
LIMIT 1

score 1 · Answer 3 · answered Nov 05 '18 at 00:52

Not going to work. The closest approach would be this, protecting your first query by wrapping into a subquery:

SELECT NUM
    FROM (SELECT 10000 + ROUND(RAND()*89999) AS NUM) AS SUBQ
    WHERE NUM NOT IN (SELECT PHONE_NUMBER FROM PHONE);

But while this does select one number between 10000 and 99999, it will be able to check for its nonexistence only afterwards. If the check fails, the query will return nothing and you'll have to retry it (or complicate it with UNIONs, which still won't give an absolute guarantee).

Another possibility would be to generate another table with all numbers between 10000 and 99999, and run a LEFT JOIN requesting that the right side is NULL. Then you have to select a random entry from that result; I'm not sure, but in this case you might have to run a ORDER BY RAND() followed by LIMIT 1 to extract one record, after potentially selecting nine hundred thousand. This is going to be costly (but see answer at the end).

The most efficient way if the phone table is almost empty would be to use a stored procedure and keep running the first query (or a generation followed by a check) until it succeeds, which if the phone table is sparse, it should do inside very few tries.

Otherwise you can pre-fill the phone table with all numbers (the table is already significantly filled, so this is not a great additional cost), adding a column that states that the number is "free". You then select a random entry from the table with the condition that the free marker yields true. In which case you'll want to read this answer.

Generate a random value that does not exist in another table MySQL

3 Answers3