联盟对表现都不好或有好处。有没有其他方法来编写查询 [英] 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 solution with 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屋！