Re: Union

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 09/14/04 

Date: Tue, 14 Sep 2004 20:30:51 +0200

There's no grouping involved. UNION removes the duplicates, quite simply. And UNION ALL does not.

However, this is not consistent with the SELECT LIST. If you don't specify ALL or DISTINCT, no
duplicate rows are removed.

To add to that, (the fact that UNION and SELECT work the opposite), you can't say UNION DISTINCT. (I
believe that you can in the latest ANSI SQL standard, though...)

Inconsistencies is what we pay for a naturally evolving language, I guess. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Michael Culley" <mculley@NOSPAMoptushome.com.au> wrote in message 
news:%236fATtfmEHA.3712@TK2MSFTNGP15.phx.gbl...
> "GG" <anonymous@discussions.microsoft.com> wrote in message
> news:18cd01c499f3$86a94780$a501280a@phx.gbl...
>> YES...  Use UNION ALL.  UNION by itself performs an
>> unnecessary grouping of your data.
>
> Thanks, that's what I thought. I guess they should have defined the UNION
> keyword a little better all those years back, maybe UNION should just join
> the results and UNION GROUP should do a union with grouping?
>
> --
> Michael Culley
>
>

Relevant Pages

  • Re: Establishing Precedence In ORDERBY Condition Causing Problems.
    ... in the query, prefix all your columns with aliases (or the table ... in the query where you use the CTE: ... of the procedure to avoid duplicates it seems to have thrown off the ...
    (comp.databases.ms-sqlserver)
  • Re: UNION ALL and SELECT DISTINCT
    ... conventions regarding duplicates in result tables. ... Notice that UNION is just the reverse. ... try to discover all the queries that run fast. ...
    (comp.databases.theory)
  • Re: Inexplicable View problem - help!!!
    ... Using UNION instead of UNION ALL has ... still change UNION to UNION ALL unless you need duplicates removed from ... >from BrandSales cross join MonthYear ...
    (microsoft.public.sqlserver.programming)
  • Re: Make a tbl qry (union) running very very slow
    ... Yes there are lots of duplicates. ... so using the 'UNION ALL' might choke the system! ... convice my mgr to get us a SQL Server DBMS so we can pull all the data into ... Access files, but not quite. ...
    (microsoft.public.access.queries)
  • Re: How Do I Get A Query to search all phone number fields in a ta
    ... Keith Bontrager - Bicycle Builder. ... If you don't want the UNION to remove duplicates, use UNION ALL and it will ... If you do want to remove duplicates, then it will take a while. ... the change of RecordSource as a user types, so all the behavior of the combo ...
    (microsoft.public.access.queries)