{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 趁早两清 的问题《To union or union all, that is the question》','https://www.manongdao.com/q-563799.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
I have two queries that I'm UNIONing together such that I already know there will be no duplicate elements between the two queries. Therefore, UNION and UNION ALL will produce the same results.
Which one should I use?
相关问题
- SQL join to get the cartesian product of 2 columns
- sql execution latency when assign to a variable
- Difference between Types.INTEGER and Types.NULL in
- php PDO::FETCH_ASSOC doesnt detect select after ba
- Faster loop: foreach vs some (performance of jsper
I would use
UNION ALLanyway. Even though you know that there are not going to be duplicates, depending on your database server engine, it might not know that.So, just to provide extra information to DB server, in order for its query planner a better choice (probably), use
UNION ALL.Having said that, if your DB server's query planner is smart enough to infer that information from the
UNIONclause and table indexes, then results (performance and semantic wise) should be the same.Either case, it strongly depends on the DB server you are using.
I see that you've tagged this question PERFORMANCE, so I assume that's your primary consideration.
UNION ALLwill absolutely outperformUNIONsince SQL doesn't have to check the two sets for dups.Unless you need SQL to perform the duplicate checking for you, always use
UNION ALL.You should use the one that matches the intent of what you are looking for. If you want to ensure that there are no duplicates use
UNION, otherwise useUNION ALL. Just because your data will produce the same results right now doesn't mean that it always will.That said,
UNION ALLwill be faster on any sane database implementation, see the articles below for examples. But typically, they are the same except thatUNIONperforms an extra step to remove identical rows (as one might expect), and it may tend to dominate execution time.Since there will be no duplicates from the two use UNION ALL. You don't need to check for duplicates and UNION ALL will preform the task more efficiently.
According to http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/ at least for performance it is better to use UNION ALL, since it does not actively distinct duplicates and as such is faster