Stack Overflow
Stack Overflow

Questions tagged [union-all]

"UNION ALL" is a keyword in SQL that is used for combining the results of multiple SELECTs. The related tag is "UNION". "UNION ALL" combines the results without checking for Uniqueness. "UNION" combines the results eliminating duplicates.

UNION ALL is a keyword in SQL that is used for combining the results of multiple SELECTs. The related tag is UNION (). UNION ALL combines the results without checking for uniqueness. UNION combines the results eliminating duplicates. The type and order of the fields in the two SELECTs should be the same.

775 questions
10
votes
2 answers

Let Oracle transform OR-connected predicates into UNION ALL operations

UNION and UNION ALL queries can outperform equivalent queries using OR-connected predicates under certain circumstances. To my knowledge, this is partially because UNION subselects can be executed in parallel and they can thus have their own…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
9
votes
1 answer

Scala Spark : How to create a RDD from a list of string and convert to DataFrame

I want to create a DataFrame from a list of string that could match existing schema. Here is my code. val rowValues = List("ann", "f", "90", "world", "23456") // fails val rowValueTuple = ("ann", "f", "90", "world", "23456") //works …
NehaM
  • 1,272
  • 1
  • 18
  • 32
9
votes
3 answers

UNION ALL two SELECTs with different column types - expected behaviour?

What is the expected behaviour due to SQL Standard when we perform UNION on two tables with different data types: create table "tab1" ("c1" varchar(max)); create table "tab2" ("c3" integer); insert into tab1 values(N'asd'), (N'qweqwe'); insert into…
alex
  • 2,252
  • 4
  • 23
  • 34
9
votes
3 answers

How to use multiple with statements along with UNION ALL in SQL?

WITH L1 AS ( SELECT ) SELECT A FROM L1 UNION ALL SELECT A FROM TABLE UNION ALL WITH L2 AS ( SELECT ) SELECT A FROM L2 UNION ALL WITH L3 AS ( SELECT ) SELECT A FROM L3 I get an error Incorrect syntax near the keyword 'with'. If this…
vijay kumar
  • 203
  • 1
  • 6
  • 16
8
votes
6 answers

SQL UNION ALL to eliminate duplicates

I found this sample interview question and answer posted on toptal reproduced here. But I don't really understand the code. How can a UNION ALL turn into a UNIION (distinct) like that? Also, why is this code faster? QUESTION Write a SQL query using…
user3685285
  • 6,066
  • 13
  • 54
  • 95
8
votes
1 answer

SSIS: Merge vs Union All

Disclaimer: I'm in the process of learning SSIS - so forgive me if this is an obvious answer, but I couldn't find anything on searching. It seems that the Merge transformation is very similar to the Union All transformation, with the following…
Omaer
  • 817
  • 1
  • 7
  • 22
8
votes
3 answers

How to use union all with manual value (not from another tabel)?

I want to use union all with manual value, not from another table. And the values are: |cSatuan1|cSatuan2|nkonversi| ============================= | LTR | PCS | 1 | | PCS | LTR | 1 | I've made the query with my own way, but…
blankon91
  • 521
  • 3
  • 15
  • 39
6
votes
2 answers

ORDER BY upper(...) with a UNION giving me problems

I'm having a bit of trouble figuring out why I'm having this problem. This code works exactly how it should. It combines the two tables (MESSAGES and MESSAGES_ARCHIVE) and orders them correctly. SELECT * FROM ( SELECT rownum as rn, a.* FROM ( …
Kosz
  • 124
  • 1
  • 9
6
votes
4 answers

When combining multiple queries into one how do you sum columns together

I'm trying to calculate League Standings from a table of Matches. +----------------------------------+ | Matches | +----------------------------------+ | id | | league_id (FK League) …
Jonathan
  • 3,016
  • 9
  • 43
  • 74
6
votes
1 answer

Oracle Optimizer issue with UNION ALL

I'm pretty confused by this behavior from Oracle optimizer. This has to do with union all operations from a CTE. If anyone has any ideas I'm all game. --Relevant data structures: --t_positionperf (index: POSITIONPERF_X1 (account_id, hist_date,…
ubanerjea
  • 474
  • 1
  • 7
  • 15
6
votes
2 answers

Union of a query with itself generates different plan

This query below: Query 1: SELECT * FROM DUAL is equivalent to and produces the same result as: Query 2: SELECT * FROM DUAL UNION SELECT * FROM DUAL This is obvious BEFORE running the two queries just by looking at them. However, it seems…
RGO
  • 4,586
  • 3
  • 26
  • 40
5
votes
3 answers

Adding DISTINCT to a UNION query

How do I get distinct title.id's from this: SELECT Title.id, Title.title FROM titles as Title HAVING points > 0 UNION ALL SELECT Title.id, Title.title FROM titles as Title HAVING points > 1 There is more to the query but this should be enough…
cdub
  • 24,555
  • 57
  • 174
  • 303
5
votes
2 answers

SQL 2000 UNION ALL ruins query optimization

I have a stored procedure which takes under a second to run normally. Users wanted data from another table in that query, so I merged that data in with a UNION ALL and a bunch of dummy columns that were missing in the new table. It worked fine in…
Bryce Wagner
  • 2,640
  • 1
  • 26
  • 43
5
votes
1 answer

Row_Number() continue in Union all query

How can i get the incremental unique number if i have 3 tables? for example: 1st query row_number result = 1,2,3 2nd query row_number result = 4,5,6 3rd query row_number result = 7,8,9 I tried the below query but from that i can get the…
A.Hemrajani
  • 63
  • 1
  • 3
5
votes
2 answers

Teradata string truncated after UNION ALL

I have a query with a UNION clause. One of the field is a plain hardcoded string. The string in the statement after UNION gets truncated to match the string length of the field before the UNION. Sounds confusing? Here's an example. SELECT 'abc' as…
idok
  • 642
  • 10
  • 24
1
2
3
…
51 52