CoderAnswer - 程序员编程问答

自定义查询以获取不在其他表中的数据

by @Joey Coder

sql

我有两个表订单与会者。两个表都有一个字段电子邮件

我正在尝试编写一个查询,让我回复参与者中未包含在表顺序中的所有电子邮件。

通常的话,我的查询会说: "Select all emails from attendees that are not included in order emails."

我尝试了以下查询,但它不像我预期的那样工作。

SELECT A.email
FROM attendee A
INNER JOIN order B ON A.order_id = B.id 
WHERE A.email NOT B.email

3个答案

1 by @forpas

选择未包含在订单电子邮件中的与会者的所有电子邮件

这是用NOT EXISTS解决的:

select a.email
from attendees a
where not exists (
  select 1 from orders
  where email = a.email
)

email列上的LEFT JOIN:

select a.email
from attendees a left join orders o 
on a.email = o.email 
where o.email is null
0 by @Adirmola

您应该轻松使用LEFT JOIN并通过过滤所有匹配案例来查找不匹配项

SELECT A.email
FROM attendee A
LEFT JOIN order B 
ON A.order_id = B.id 
WHERE B.email IS NULL
0 by @Joey Coder

我最终使用Django来生成Query,因为我可以更好地处理它。工作结果如下。谢谢你的帮助。

(SELECT "attendee"."email" FROM "attendee" ORDER BY "attendee"."created" DESC) EXCEPT (SELECT "order"."email" FROM "order" ORDER BY "order"."created" DESC)

相关问题