联盟对表现都不好或有好处。有没有其他方法来编写查询 [英] is union all bad or good for performance. Is there any other way to write the query

查看:89
本文介绍了联盟对表现都不好或有好处。有没有其他方法来编写查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

- 我的项目中有以下场景......但我举一个简单的例子。请帮助。



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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆