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                                              |
+-----------------------------------------------------+
| "[email protected]" |
| "[email protected]"                       |
| "[email protected]"                      |
| "[email protected]"                          |
| "[email protected]"                     |
| "[email protected]"                          |
| "[email protected]"                         |
| "[email protected]"                   |
| "[email protected]"                        |
| "[email protected]"                 |
+-----------------------------------------------------+
10 rows in set (0.00 sec)

Blocks

mysql> select * from blocks limit 10;    
+---------------------------------------------------+
| email                                             |
+---------------------------------------------------+
| ""                                                |
| [email protected] |
| [email protected]                                |
| [email protected]                                |
| [email protected]                                |
| [email protected]                     |
| [email protected] |
| [email protected] |
| [email protected] |
| [email protected] |
+---------------------------------------------------+
10 rows in set (0.00 sec)

Expected result from Sent table

+-----------------------------------------------------+
| emails                                              |
+-----------------------------------------------------+
| "[email protected]" |
| "[email protected]"                      |
| "[email protected]"                          |
| "[email protected]"                     |
| "[email protected]"                          |
| "[email protected]"                         |
| "[email protected]"                   |
| "[email protected]"                        |
| "[email protected]"                 |
+-----------------------------------------------------+

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. [email protected] 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