联盟对表现都不好或有好处。有没有其他方法来编写查询 [英] is union all bad or good for performance. Is there any other way to write the query
问题描述
- 我的项目中有以下场景......但我举一个简单的例子。请帮助。
table1
字段:
1)id < br $>
2)距离
3)地址
table2
字段:
1)id
2)colony
3)subcolony
- 问题..
现在查询必须以下列方式编写:
----->它应该从表2中获取不在table1中的所有id
----->它应该从table1中的表2中获取所有id,但距离为零
- 来自我的想法乍看之下是:
--I have the below scenario in my project...But i am giving a simple example. Kindly help.
table1
fields :
1) id
2) distance
3) address
table2
fields:
1) id
2) colony
3) subcolony
--question..
now a query has to be written in such a way that :
-----> it should fetch all the id from table 2 that is not in table1
-----> it should fetch all the id from table 2 that is in table1 but the distance is zero
--The idea which came to my mind at first glance is :
select id from table2
where id not in(select id from table1)
union all
select id from table2
where id in(select id from table1 where distance=0)
- 还有其他方法可以解决。有没有其他方法可以写这个查询?
请需要你的帮助。
--So is there any other way to tackle. Is there any other way to write this query ?
Kindly need your help.
推荐答案
第二个解决方案CTE 的:
Second solutionwith CTE:
SELECT id FROM table2
WHERE (id NOT IN (SELECT id FROM table1))
OR (id IN (SELECT id FROM table1 WHERE distance=0))
乍一看我想过一个CTE,但它不会删除联盟。
这个解决方案应该没有UNION。
I thought about a CTE at first glance, but it would not have removed the union.
This solution should work without UNION.
这可以做你需要的,除了你做的小错误查询结束:
This can do what you need, except you made as small mistake in the end of the query:
SELECT id FROM table2
WHERE id NOT IN(SELECT id FROM table1)
UNION
SELECT id FROM table2
WHERE id IN(SELECT id FROM table1 WHERE distance=0)
我将'和'替换为'where'。
I replaced 'and' with 'where'.
这篇关于联盟对表现都不好或有好处。有没有其他方法来编写查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!