Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [union]

Combines two or more query results. UNION removes duplicates; UNION ALL retains all rows.

218 questions
13
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,162
  • 1
  • 14
  • 18
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
  • 29,978
  • 4
  • 56
  • 131
11
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
  • 269
  • 1
  • 2
  • 14
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

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
8
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
  • 183
  • 1
  • 2
  • 6
8
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,793
  • 8
  • 29
  • 40
8
votes
2 answers

Most efficient way to generate a diff

I have a table in SQL server that looks like this: Id |Version |Name |date |fieldA |fieldB ..|fieldZ 1 |1 |Foo |20120101|23 | ..|25334123 2 |2 |Foo |20120101|23 |NULL ..|NULL 3 |2 …
ame
  • 83
  • 5
7
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
  • 121
  • 2
  • 2
  • 3
7
votes
2 answers

Why won't SQL Server optimize the UNIONs?

Consider these queries (SQL Fiddle): Query 1: SELECT * INTO #TMP1 FROM Foo UNION SELECT * FROM Boo UNION SELECT * FROM Koo; Query 2: SELECT * INTO #TMP2 FROM Foo UNION SELECT * FROM Boo UNION ALL SELECT * FROM Koo; Note that Koo does not overlap…
孔夫子
  • 4,258
  • 3
  • 26
  • 49
7
votes
2 answers

Filtering UNION ALL result is much slower than filtering each subquery

(edit: see end for a simpler example) I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), which means that for these records a mix of…
Zilk
  • 1,011
  • 2
  • 9
  • 12
6
votes
3 answers

Optimize UNION query in MySQL

I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, more than 35 years of age. We are using "vertical…
François
  • 61
  • 1
  • 4
6
votes
2 answers

Perform UNION ALL regardless of column order in the two joined tables

SELECT * INTO TABLE1 FROM Table2 UNION ALL SELECT * FROM Table3; GO I am using this query to stack two tables together into one table. These tables should have the columns in the exact same order for this query to execute successfully. I am…
Anna Tomasson
  • 61
  • 1
  • 2
5
votes
3 answers

Union implemented with Hash Match operator

I was reviewing the SQL Server physical operators listed on TechNet (don't judge, you know you've done it) and read that the Hash Match physical operator is sometimes used to implement the UNION logical operator. I have never seen that done and…
sam.bishop
  • 474
  • 3
  • 15
1
2 3
…
14 15