Mysql全外连接

Question

我有一个名为“电子邮件”的表:

     email          domain         timestamp
abcd@google.com    google.com      2019/12/08
abd@google.com     google.com      2019/12/08
abcd@google.com    google.com      2019/12/09
abd@google.com     google.com      2019/12/09
123@google.com     google.com      2019/12/09

我只想输出与上一个日期相比新的电子邮件，如下所示:

       email        domain      timestamp
123@google.com     google.com   2019/12/09

因为 mysql 没有完整的外部连接，我正在使用以下查询，但它不起作用:

 select * from email e1
 left join emails e2
 on e1.domain_searched = e2.domain_searched 
 where
 e1.timestamp = '2019/12/08'

 union

 select * from email e1
 right join emails e2
 on e1.domain_searched = e2.domain_searched 
 where
 e1.timestamp = '2019/12/09';

伙计们，我需要你的建议......提前谢谢你......

Answer 1

i am looking for only emails that do not have timestamp prior in my db or in other way, it was not in my db at all before

NOT EXISTS:

select * from emails e
where e.timestamp = '2019/12/09'
and not exists (
  select 1 from emails
  where email = e.email and timestamp < e.timestamp
);  

参见 demo .
结果:

| email          | domain     | timestamp  |
| -------------- | ---------- | -----------|
| 123@google.com | google.com | 2019-12-09 |