Why NOT IN didn't work

Question

Originally, I want to create a some sort of id for user, but I can't change my data table into unique for several reason. So I have to generate unique random data manually, so I decide to do this:

SELECT CONCAT("B",FLOOR(10000 + RAND() * 89999)) AS random_number
FROM mmr
WHERE "random_number" NOT IN (SELECT rm FROM mmr) LIMIT 1

with FLOOR(10000 + RAND() * 89999) I was able to get random data between 10000-99999 but it's still not unique.

mmr is large table with more than 2M data and rm type is varchar(12). The result I was expecting is Bxxxxx

I also tried to use not exists but no result came in

The `SELECT` part is only executed once. You'd have to run that query over and over again, until a `random_number` happens to be unused. Very inefficient. — DanMan, Apr 10 '15 at 07:38
Wait, so you want 2 000 000 unique numbers between 10 000 and 99 999? Some algorithm that would be **;)** — asontu, Apr 10 '15 at 08:31

score 1 · Accepted Answer · edited May 23 '17 at 11:43

1

You're trying to refer to the value that was randomly generated in the SELECT part by referencing its random_number alias in the WHERE clause. No can do. As well you are possibly comparing random_number with a NULL field because of your NOT IN condition. This will evaluate to NULL (or effectively FALSE).

You could try:

SELECT random_number
FROM (
    SELECT CONCAT("B",FLOOR(10000 + RAND() * 89999)) AS random_number
    FROM mmr
) t
WHERE t.random_number NOT IN (
    SELECT rm
    FROM mmr
    WHERE rm IS NOT NULL
)
LIMIT 1

Now the inner query will generate as much random numbers as there are records in mmr, but within that query might already be duplicates. After that any numbers that were already in mmr get filtered out, leaving you with fewer random numbers than there are records in mmr.

(I asked about 'B' NOT IN ('A', NULL) once here on SO and got some good comments explaining it)

edited May 23 '17 at 11:43

Community

1
1

answered Apr 10 '15 at 07:13

asontu

4,548
1
21
29

`"random_number"` this should behave as string not variable. – Girish Apr 10 '15 at 07:20
@Girish doesn't change the behavior, effectively your asking _'Is column (or string) "random_number" absent in this set of values which contains an unknown value?"_ and MySQL says _'I can't know for sure, maybe the unknown value is equal to "random_number", so I'm gonna say `null`'_. Now in SQL Server `"` denotes a column-name that has special chars, I do believe in MySQL that would be the backtick ` in stead. – asontu Apr 10 '15 at 07:25
anyway op added mysql tag in question, if you use as variable then the query will return error `#1054 - Unknown column 'random_number' in 'where clause' ` because random_number is not table fields – Girish Apr 10 '15 at 07:30
also, adding `rm IS NOT NULL` still gave me non unique results – exatma Apr 10 '15 at 07:36
I expanded the answer substantially, but I'm wondering about the ultimate goal here. Could you not simply have ascending numbers starting from `B10000`? If you absolutely need them to be random, I think you're going to have to generate 1 unique number, insert it, generate a new number, see if it exists, if not insert it, else discard it, continue until you've generated enough random numbers that weren't discarded for existing... As well, 2 000 000 unique numbers between 10 000 and 99 999? ehhh....? – asontu Apr 10 '15 at 08:30
I didn't say in my question but `Bxxxxx` is only part of user, there are also `xxxxxx` and others. Some even have double id but it's not my problem. This `Bxxxxx` user already exist and the number is random, I can't get top number and add +1, since there are so many unused number. Anyway, your query is working, thanks alot :) – exatma Apr 10 '15 at 08:41

Piero Alberto · Answer 2 · 2015-04-10T07:27:51.277

0

try this:

SELECT CONCAT("B",FLOOR(10000 + RAND() * 89999)) AS random_number
FROM mmr
WHERE random_number NOT IN (SELECT rm FROM mmr WHERE rm IS NOT NULL)

EDIT: why "limit 1" was in your code? I have deleted it, not useful in this case.. it could be the cause of your issue, because it takes only one record

edited Apr 10 '15 at 07:27

answered Apr 10 '15 at 07:10

Piero Alberto

3,823
6
56
108

why we use @with field name? – Deep Kakkar Apr 10 '15 at 07:13
because is not a field but var, I think.. i have edited my answer – Piero Alberto Apr 10 '15 at 07:14
you can't use defined variable in same query condition, because select condition proceed first then emerged result, according your query condition depend on query result – Girish Apr 10 '15 at 07:19
I use LIMIT1 because I want to generate one unique id. And thanks for the answer but it still doesn't work. – exatma Apr 10 '15 at 07:30
and if you put "..."? – Piero Alberto Apr 10 '15 at 07:32

Girish · Answer 3 · 2015-04-10T08:53:20.590

0

You can use CROSS JOIN with query and check random_number exist in table rows

SELECT random_number
FROM mmr
CROSS JOIN (SELECT CONCAT("B",FLOOR(10000 + RAND() * 89999)) AS random_number) rmt
WHERE random_number <> rm AND rm IS NOT NULL
LIMIT 1

edited Apr 10 '15 at 08:53

answered Apr 10 '15 at 07:38

Girish

11,907
3
34
51

it give me error `SQL execution error #1064` I tried to rewrite it several ways but still no luck – exatma Apr 10 '15 at 08:09

Why NOT IN didn't work

3 Answers3