MySQL Matching records across tables discrepancies

Question

I have a database called match, within this database I have a number of tables. Of which in particular I need to marry up two datasets in different tables to identify email addresses that are not contained within the other. I recently had to marry some data so I built a Perl script in terminal to do this, sadly the file I was trying to match had email address as mixed case so I have a discrepancy of around 2,000 rows. Rather than rebuilding the script and waiting hours for it to run using Perl I opted to load it into tables to make my life easier.

The tables in question are called:

• clickwithcust - customers I want to exclude - 18,978 records in total.

• clickwithoutcust - contains all email addresses from both clickwithcust and the remaining dataset that i need. - 20,932 records in total.

I ran a query which gives me everyone that has an email address and customer key:

SELECT *
FROM `click_withcust`
INNER JOIN click_withoutcust
ON click_withcust.`email`=click_withoutcust.`email`;

Resulting in 18820 unique rows

Then I wanted to find the inverse of this query so I created the following query:

SELECT *
FROM `Click_withoutcust`
WHERE NOT EXISTS (SELECT DISTINCT `email`
FROM click_withcust WHERE `Click_withoutcust`.`email`= click_withcust.`email`);

This should return the unique number of missing email addresses from the table click_withoutcust. Sadly I get a figure of 2,175 records when in theory I should get 1,954.

I'm at a bit of a lost end here and could really do with some help.

Answer 1

I think this could be a whitespaces issue. The problem is that MySQL ignores trailing whitespace when doing string comparison. See http://dev.mysql.com/doc/refman/5.6/en/char.html

All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces.

...

For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error.

The section for the like operator gives an example for this behavior (and shows that like does respect trailing whitespace):

mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
|          1 |             0 |
+------------+---------------+
1 row in set (0.00 sec)

Unfortunately the UNIQUE index seems to use the standard string comparison to check if there is already such a value, and thus ignores trailing whitespace. This is independent from using VARCHAR or CHAR, in both cases the insert is rejected, because the unique check fails. If there is a way to use like semantics for the UNIQUE check then I do not know it.

I made a test so you could see if there's some fields with whitespace that you're trying to matching.

Tables:

CREATE TABLE `click_withcust` (
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `click_withoutcust` (
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Data:

mysql> select * from test.click_withoutcust;
+--------------------+
| email              |
+--------------------+
| onaaree@gmail.com  |
| onare123@gmail.com |
| onare@gmail.com    |
+--------------------+
3 rows in set (0.00 sec)

mysql> select * from test.click_withcust;
+------------------+
| email            |
+------------------+
|  onare@gmail.com |
| onare@gmail.com  |
+------------------+
2 rows in set (0.00 sec)

mysql> 

If you see there's a row with one whitespace onare@gmail.com.

SELECT click_withoutcust.*
FROM click_withoutcust
LEFT JOIN click_withcust
ON (click_withcust.`email`=click_withoutcust.`email`)
WHERE click_withcust.email IS NULL;

mysql> SELECT 
    -> click_withoutcust.*
    -> FROM click_withoutcust
    -> LEFT JOIN click_withcust
    -> ON (click_withcust.email=click_withoutcust.email)
    -> WHERE click_withcust.email IS NULL;
+--------------------+
| email              |
+--------------------+
| onaaree@gmail.com  |
| onare123@gmail.com |
+--------------------+
2 rows in set (0.00 sec)

But if you count the tables rows:

mysql> select count(*) from test.click_withoutcust;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test.click_withcust;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Try using COUNT and LIKE to find if there are rows with whitespaces.

mysql> select count(*) from test.click_withoutcust where email like '% %';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from test.click_withcust where email like '% %';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)