Is the counter (excellent directory_name) different from counting the lines of a single request?

Question

I have a table with 11 million rows in a MySQL database. One of the columns is a personal identification number. People are listed many times in the table, and I want to know how many unique personal identification numbers are. And then create a table of these unique numbers. When I count individual personal identification numbers from a column, I get a different number than when I insert them directly into the table. For instance:

select count(distinct person_key) from big_table; 

gives me the bill 4074890.

Then, when I try to create a table with them,

 insert into new_table select distinct person_key from big_table; 

it creates only 2,701,875 lines.

(Also, if I use the query: select count(1) from (select distinct person_key from big_table) temp; it gives me 2,701,875.)

Any ideas what I'm doing wrong?

Answer 1

COUNT (DISTINCT expr, [expr ...])

Returns the number of rows with different values ​​other than NULL expr

The MySQL documentation talked about this. So I think person_key has NULL or duplicate values