Combines two or more query results. UNION removes duplicates; UNION ALL retains all rows.
Questions tagged [union]
237 questions
17
votes
1 answer
Union does not always eliminate duplicates
I have the following query and expect that as a result I will have a list of IDs without duplicates. But sometimes it produces duplicates (1-2 on 4 million rows).
Why can it happen? I run it with the default (read committed) isolation level. I can't…
Novitskiy Denis
- 331
- 1
- 11
15
votes
3 answers
Concatenation Physical Operation: Does it guarantee order of execution?
In standard SQL, the result of a union all is not guaranteed to be in any order. So, something like:
select 'A' as c union all select 'B'
Could return two rows in any order (although, in practice on any database I know of, 'A' will come before…
Gordon Linoff
- 2,252
- 1
- 17
- 18
12
votes
3 answers
Get table name from union query?
This is my query
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1
union
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 where Active =1
union
SELECT Id, productName,…
Prashant Tapase
- 279
- 1
- 2
- 15
12
votes
3 answers
Are results from UNION ALL clauses always appended in order?
As per standard SQL UNION / UNION ALL do not guarantee any particular sort order without an outer ORDER BY clause - like there is hardly any place in SQL where sort order is guaranteed without ORDER BY.
However, Postgres uses an "Append" step for…
Erwin Brandstetter
- 185,527
- 28
- 463
- 633
12
votes
1 answer
How can I prevent UNPIVOT from being transformed into UNION ALL?
I have a somewhat complex Oracle query which is taking about half an hour to complete. If I take the slow part of the query and run it separately it finishes in a few seconds. Here's is a screenshot of the SQL Monitor report for the isolated…
Joe Obbish
- 32,976
- 4
- 74
- 153
11
votes
1 answer
MySQL: Optimize UNION with "ORDER BY" in inner queries
I just set up a logging system which consists of multiple tables with the same layout.
There is one table for each data source.
For the log viewer, I want to
UNION all the log tables,
filter them by account,
add a pseudo column for identification…
Lukas
- 213
- 1
- 2
- 10
10
votes
2 answers
Using COLLATE with UNION
How to use COLLATE with UNION? I want to union 2 tables (both have the same columns and the same types: varchar,int, int, decimal).
I got the following error:
sg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between…
Anja
- 151
- 2
- 2
- 4
10
votes
4 answers
Can I combine the results from multiple columns into a single column without UNION?
I have a table with several columns which I want to SELECT:
SELECT his_name , her_name, other_name FROM foo;
Bu, I instead I want to combine the results all into a single column.
As an example, I can do this with UNION ALL as
SELECT her_name AS…
igx
- 203
- 1
- 3
- 7
10
votes
2 answers
Microsoft SQL Server 2014 Nested From Query in Cross-Apply
When selecting from a nested query in an OUTER APPLY statement the nested query seems to be evaluated only once in certain circumstances.
Bug reported to Azure Feedback Forum:…
Phist0ne
- 103
- 5
10
votes
2 answers
Problem with union casting integer to ceiling(decimal)
I have this scenario, it looks like MySQL is taking the largest decimal value and tries to cast the other values to that.
The problem is that this query is generated by an external library, so I don't have control over this code, at this level at…
ngcbassman
- 103
- 5
9
votes
2 answers
How to make a union view execute more efficiently?
I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process every night.
In several places in our code we need…
Shaul Behr
- 2,963
- 8
- 34
- 42
8
votes
1 answer
Order and nature of columns in select list of sorted UNION query affects performance
I am observing significant performance impact of select lists in sorted UNION queries.
The general form of UNION queries I am working with is:
SELECT * FROM (
SELECT FROM
UNION ALL
...
) q
ORDER BY
