具有多个 select 语句的子查询

发布于 2024-11-08 16:26:26 字数 274 浏览 2 评论 0原文

检查子查询在“不在”条件内是否有多个 select 语句，

例如。

select id from tbl where 
id not in (select id from table1) and 
id not in (select id from table2) and
id not in (select id from table3)

我需要子查询，而不是重复相同的 id“不符合”条件，它将从多个表中一次性签入..

请帮助..

原文

to check the subquery having multiple select statement inside 'not in' condition

Eg.

select id from tbl where 
id not in (select id from table1) and 
id not in (select id from table2) and
id not in (select id from table3)

instead of repeating the same id 'not in' condition , i need the subquery which will check in one shot from multiple tables..

pls help..

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

御守 2024-11-15 16:26:27

您的查询最好表达为：

SELECT id 
FROM tbl t
LEFT JOIN table1 t1 on t1.id = t.id 
LEFT JOIN table2 t2 on t2.id = t.id 
LEFT JOIN table3 t3 on t3.id = t.id 
WHERE t1.id IS NULL AND t2.id IS NULL AND t3.id IS NULL

Your query is better expressed as:

SELECT id 
FROM tbl t
LEFT JOIN table1 t1 on t1.id = t.id 
LEFT JOIN table2 t2 on t2.id = t.id 
LEFT JOIN table3 t3 on t3.id = t.id 
WHERE t1.id IS NULL AND t2.id IS NULL AND t3.id IS NULL

回复收藏 0 原文

冰火雁神 2024-11-15 16:26:27

您可以使用联合，这样您就只有一个 in：

select  id
from    tbl 
where   id not in 
        (
        select id from table1
        union all select id from table2
        union all select id from table3
        )

注意：not in 不适用于可空列，但我假设 id此处不可为空。

You could use a union, so you just have one in:

select  id
from    tbl 
where   id not in 
        (
        select id from table1
        union all select id from table2
        union all select id from table3
        )

Note: not in does not work well with nullable columns, but I assume id is not nullable here.

回复收藏 0 原文

像极了他 2024-11-15 16:26:27

使用全部联合
像这样-->
select f.FIRST_NAME from farmer f where f.ID in (select v.ID from Village v where v.ID in (1,2) union all select s.ID from state s where s.ID in (3,4) )

回复收藏 0 原文

~没有更多了~