How to SELECT none matching rows from one table to another?

Question

Mysql v5.7 I know this question is asked and answered before but the general queries is not working in my database and im pulling my hair off. I have two tables that only have one column containing emails : 1) Sent (41110 rows [there are duplicates]) 2) Blocks (81132 [there are duplicates])

mysql> show create table blocks;
+--------+------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                   |
+--------+------------------------------------------------------------------------------------------------+
| blocks | CREATE TABLE `blocks` (
  `email` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------------------------+

mysql> show create table sent;
+-------+-----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                  |
+-------+-----------------------------------------------------------------------------------------------+
| sent  | CREATE TABLE `sent` (
  `emails` varchar(111) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------+

I want to get rows as a result of "Sent" - "Blocks" OR I want to get those rows from "Sent" table that is not in "Blocks" table

Sent table:

mysql> select * from sent limit 10;
+-----------------------------------------------------+
| emails                                              |
+-----------------------------------------------------+
| "52@mail.marketplace" |
| "1cstains@coden.com"                       |
| "a.aldhizer@ldplastics.com"                      |
| "a.antosca@tdx.com"                          |
| "a.balvanz@hrrd-paper.com"                     |
| "a.bandal@medpace.com"                          |
| "a.bass@paettethanol.com"                         |
| "a.bodwalk@bedfndustries.com"                   |
| "a.bravo@sdfhemie.com"                        |
| "a.burton@evdarenterprises.com"                 |
+-----------------------------------------------------+
10 rows in set (0.00 sec)

Blocks

mysql> select * from blocks limit 10;    
+---------------------------------------------------+
| email                                             |
+---------------------------------------------------+
| ""                                                |
| 0f6a88be0a4b45628be38ae08c8fdd71@mail.marketplace |
| 1cstains@coden.com                                |
| 1cstains@coden.com                                |
| 1cstains@coden.com                                |
| 3161foodmgr@mcare.com                     |
| 4b00fce87e5b423c942f5b19f27c3a13@mail.marketplace |
| 52d05d98b59e44b0816401d2cd0411f0@mail.marketplace |
| 52d05d98b59e44b0816401d2cd0411f0@mail.marketplace |
| 6357a2fd35114418a93e0ccda6edd6f4@mail.marketplace |
+---------------------------------------------------+
10 rows in set (0.00 sec)

Expected result from Sent table

+-----------------------------------------------------+
| emails                                              |
+-----------------------------------------------------+
| "52@mail.marketplace" |
| "a.aldhizer@ldplastics.com"                      |
| "a.antosca@tdx.com"                          |
| "a.balvanz@hrrd-paper.com"                     |
| "a.bandal@medpace.com"                          |
| "a.bass@paettethanol.com"                         |
| "a.bodwalk@bedfndustries.com"                   |
| "a.bravo@sdfhemie.com"                        |
| "a.burton@evdarenterprises.com"                 |
+-----------------------------------------------------+

Here is the query i used and it returns matching rows :/

select sent.emails from sent where sent.emails NOT IN ( select email from blocks )

Add some sample table data and the expected result - as formatted text. — jarlh, Aug 24 '17 at 12:56
Try this - select sent.emails from sent where sent.emails NOT IN ( select distinct email from blocks ) — Ankit Bajpai, Aug 24 '17 at 13:05
@AnkitBajpai it is returning same result. These queries are showing the rows thats not in the table "Sent" . The same query is working for tables with 10 rows each — sherpaurgen, Aug 24 '17 at 13:11
All addresses are different. in one table you have double quotes arround. — Jens, Aug 24 '17 at 13:16
@satch_boogie what is the o/p you got when you are firing your query! — Mr. Go, Aug 24 '17 at 13:17
oh! crap i think this is due to those double quotes, i forgot those — sherpaurgen, Aug 24 '17 at 13:19

score 0 · Accepted Answer · answered Aug 24 '17 at 13:22

0

select sent.emails 
from sent 
     left join blocks on sent.emails = blocks.email
where blocks.email is null

if the problem is " char in emails (as @Jens was mentioned) you can change join to cut the " char using subbstring to skip first and last chars

left join blocks on SUBSTRING(sent.emails,1,len(sent.emails)-2) = blocks.email

answered Aug 24 '17 at 13:22

StanislavL

56,971
9
68
98

You can also use `replace` function to remove the double quotes, it is safer if there are emails without double quotes too – Jens Aug 24 '17 at 13:32
@Jens you are right. I consider `Replace` could be dangerous when string contains `"` internally. Not remember whether `"` is allowed in emails though – StanislavL Aug 24 '17 at 13:38
I do not think double quotes are valid characters in email addresses – Jens Aug 24 '17 at 13:39
@Jens https://stackoverflow.com/questions/2049502/what-characters-are-allowed-in-an-email-address looks like `"` is allowed. - **e.g. John..Doe@example.com is not allowed but "John..Doe"@example.com is allowed** – StanislavL Aug 24 '17 at 13:44
Yes i have seen it in the same Moment on Wikipedia. But both versions of the SQL are risky without analyseing the data. – Jens Aug 24 '17 at 13:45

score -1 · Answer 2 · answered Aug 24 '17 at 13:08

-1

Please use minus Operator.

select sent.emails from sent 
minus
select blocks.email from blocks

answered Aug 24 '17 at 13:08

SQL.RK

157
4

this is mysql server and doesnt support "minus" – sherpaurgen Aug 24 '17 at 13:09

How to SELECT none matching rows from one table to another?

2 Answers2