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.

Filter by
Sorted by
Tagged with
1705 votes
19 answers
1.6m views

What is the difference between UNION and UNION ALL?

What is the difference between UNION and UNION ALL?
Brian G's user avatar
  • 54.4k
65 votes
4 answers
134k views

Combining ORDER BY AND UNION in SQL Server

How can I get first record of a table and last record of a table in one result-set? This Query fails SELECT TOP 1 Id,Name FROM Locations ORDER BY Id UNION ALL SELECT TOP 1 Id,Name FROM Locations ...
Faizal's user avatar
  • 1,743
42 votes
2 answers
59k views

How can I do a Union all in Entity Framework LINQ To Entities?

I came across a scenario where I had to use Union all, how can I achieve so in LINQ to entities ?
Rami Sakr's user avatar
  • 586
36 votes
10 answers
120k views

How to execute UNION without sorting? (SQL)

UNION joins two results and remove duplicates, while UNION ALL does not remove duplicates. UNION also sort the final output. What I want is the UNION ALL without duplicates and without the sort. Is ...
hightow's user avatar
  • 779
31 votes
2 answers
9k views

Curious issue with Oracle UNION and ORDER BY

The following query is perfectly valid in pretty much every database (give or take a dual dummy table), including Oracle: select 'A' as x from dual union all select 'B' from dual order by x asc ...
Lukas Eder's user avatar
  • 216k
28 votes
6 answers
42k views

Performance of UNION versus UNION ALL in SQL Server

I have to run a SELECT statement across several tables. I am sure the tables return different records. I am anyway using UNION ALL. Is it better to use UNION or of UNION ALL in performance terms when ...
UnDiUdin's user avatar
  • 15.1k
27 votes
3 answers
26k views

SQL Union All with order by and limit (Postgresql)

In the following query I get syntax error: SELECT <property1>, <property2> FROM <table1> ORDER BY <condition> LIMIT 1 UNION ALL SELECT <property1>, <property2> ...
michael's user avatar
  • 3,905
23 votes
1 answer
49k views

UNION types text and bigint cannot be matched

I'm running a complex stored procedure and I'm getting an error when I have 3 unions, but with 2 unions no error. If I remove either of the top two unions it runs fine. If I make one of the NULLs a 0, ...
Stephane's user avatar
  • 1,663
16 votes
3 answers
43k views

Why would I want .union over .unionAll in Spark for SchemaRDDs?

I'm trying to wrap my head around these two functions in the Spark SQL documentation– def union(other: RDD[Row]): RDD[Row] Return the union of this RDD and another one. def unionAll(otherPlan: ...
duber's user avatar
  • 2,809
15 votes
5 answers
34k views

Same number of columns for Union Operation

I was going through union and union all logic and trying examples. What has puzzled me is why is it necessary to have same number of columns in both the tables to perform a union or union all ...
Sparky's user avatar
  • 753
15 votes
8 answers
31k views

Slow query on "UNION ALL" view

I have a DB view which basically consists of two SELECT queries with UNION ALL, like this: CREATE VIEW v AS SELECT time, etc. FROM t1 // #1... UNION ALL SELECT time, etc. FROM t2 // #2... The ...
Mladen Jablanović's user avatar
14 votes
1 answer
73k views

Group By and Order By with UNION ALL

I have a stored procedure with the following query: SELECT (sum(addition)) AS [COUNT], MAX(CONVERT(VARCHAR(12),CREATED,102)) as [date] FROM [TABLE_ONE] WHERE convert(VARCHAR(12),CREATED,102) BETWEEN ...
HelpASisterOut's user avatar
13 votes
5 answers
28k views

SQL Server, Using UNION ALL for multiple tables then paging implementation

I need help also about paging and using UNION ALL for multiple tables: How do i implement an optimized paging when joining multiple tables using UNION ALL and returning only specific number of rows......
Robert Dominic Angelo's user avatar
12 votes
4 answers
25k views

SQL Server: ORDER BY in subquery with UNION

i have two queries being combined with a UNION ALL1: --Query 1 SELECT Flavor, Color FROM Friends   --Query 2 SELECT Flavor, (SELECT TOP 1 Color FROM Rainbows WHERE Rainbows....
Ian Boyd's user avatar
  • 252k
10 votes
5 answers
1k views

To union or union all, that is the question

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 ...
Billy ONeal's user avatar
10 votes
2 answers
3k views

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 ...
Lukas Eder's user avatar
  • 216k
9 votes
3 answers
35k views

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 ...
vijay kumar's user avatar
9 votes
3 answers
58k views

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 ...
alex's user avatar
  • 2,382
9 votes
1 answer
52k views

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 = ("...
NehaM's user avatar
  • 1,302
8 votes
1 answer
14k views

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 ...
Omaer's user avatar
  • 817
8 votes
3 answers
17k views

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 | ...
blankon91's user avatar
  • 515
8 votes
6 answers
25k views

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 ...
user3685285's user avatar
  • 6,336
7 votes
3 answers
32k views

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 > ...
cdub's user avatar
  • 25.2k
6 votes
2 answers
968 views

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 ...
RGO's user avatar
  • 4,667
6 votes
2 answers
9k views

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. ...
Kosz's user avatar
  • 169
6 votes
4 answers
214 views

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 ...
Jonathan's user avatar
  • 3,024
6 votes
1 answer
4k views

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 (...
ubanerjea's user avatar
  • 474
5 votes
2 answers
9k views

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. ...
idok's user avatar
  • 642
5 votes
2 answers
6k views

MySQL performance of VIEW for tables combined with UNION ALL

Let's say I have 2 tables in MySQL: create table `persons` ( `id` bigint unsigned not null auto_increment, `first_name` varchar(64), `surname` varchar(64), primary key(`id`) ); ...
Yuriy Nakonechnyy's user avatar
5 votes
2 answers
7k views

Create a UNION ALL query when the columns are in different order

I have 600 tables to perform a UNION ALL query on. Unfortunately the order of the columns in each table varies, however they will always have the same name - example: Table 1 Item, Cost, ID, ...
James2086's user avatar
  • 231
5 votes
1 answer
4k views

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 ...
A.Hemrajani's user avatar
5 votes
3 answers
2k views

Don't repeat yourself: same SQL query, but two different tables

I have an SQL query with exact the same code, but two different tables (AUDIT_TRAIL_ARCHIVE and AUDIT_TRAIL). I use "UNION ALL" to have one result. Good programmers use "Don't repeat yourself" ...
Sybil's user avatar
  • 2,527
5 votes
1 answer
3k views

How do I continue the process after inserting non-matching lookup rows into a table?

Inside a data flow, is it possible to do a lookup, insert the non-matching rows, re-lookup and then continue with the full (original) data set? I can't see how you get/re-use the inserted rows after ...
PeterX's user avatar
  • 2,821
5 votes
5 answers
3k views

MSSQL - union all with different decimal precision

When I execute this SQL SELECT 1.4 UNION ALL SELECT 2.0400 union all SELECT 1.24 I get the following result: 1.4000 2.0400 1.2400 But when I execute the following SQL SELECT sum(1.4) UNION ALL ...
Armen Nazarian's user avatar
5 votes
2 answers
1k views

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 ...
Bryce Wagner's user avatar
  • 2,660
4 votes
3 answers
70 views

How can I display rows values in columns sql server?

SQL FIDDLE DEMO HERE I have this table structure for SheduleWorkers table: CREATE TABLE SheduleWorkers ( [Name] varchar(250), [IdWorker] varchar(250), [...
Esraa_92's user avatar
  • 1,558
4 votes
3 answers
790 views

Is the order of union all guaranteed

Is it guaranteed that order of two parts of the union all query will be given in the particular order? I.e. that the result of this query: select 'foo' from dual union all select 'bar' from dual ...
Ivan Sopov's user avatar
  • 2,340
4 votes
2 answers
11k views

How to union SELECT two tables with ids of both tables?

Ok, I have four tables: Table 1: "f_withholdings" Table 2: "f_wh_list" Table 3: "f_rpayments" Table 4: "f_rp_list" Table 1 and Table 2 are connected with each other by wh_id field and Table 3 and ...
Darkhan's user avatar
  • 1,278
4 votes
1 answer
8k views

MS Access SQL: Using SELECT INTO with a UNION ALL query

I'm attempting to turn an already working UNION query into a SELECT INTO as part of a VBA procedure for a bunch of temp tables. I'm doing this because Access is having performance issues. When I try ...
plankton's user avatar
  • 323
4 votes
4 answers
10k views

Laravel fetch data from two tables without join with pagination

I want to fetch results from two tables properties and properties_x where properties.address or properties_x.address_x like test with laravel pagination. There is no foreign key relationship between ...
Harpal Singh's user avatar
4 votes
3 answers
23k views

Union in JPA query - from the same table

I have a requirement where I need to restrict number of records returned from a table for a particular flag and all records for the other flag value. For example: contact-history table has an element ...
Kevin Rave's user avatar
  • 14.1k
4 votes
2 answers
9k views

Using Union All and Order By in MySQL

I've 2 tables: create table advertised_products(id int,title varchar(99),timestamp timestamp); insert advertised_products select 1,'t1',curdate(); create table wanted_products(id int,title varchar(...
Muhammad Raheel's user avatar
4 votes
2 answers
520 views

How does MySQL UNION ALL run agregate functions?

I have some SQL code which is working exactly how I want it to: select 10 as number, "Checklist 10 Foo" as name, max(id), max(ts) as max_ts, callsign, max(time_hint_utc), count(*) from checklist_10 ...
fadedbee's user avatar
  • 43.8k
4 votes
2 answers
3k views

MS VBA with loops and unions

Dim Counter As Integer Dim Maxhouse As Integer Dim FindHouse As Range Dim RangeVar As Range Dim HousesRange As Range For Counter = 1 To MaxHouse ActiveSheet.Cells(16, 2 + Counter).Select ...
user3072955's user avatar
4 votes
1 answer
80 views

SQL Server (2014) Odd behaviour from variations on IN (list)

I've been looking at trying to optimise (or at least change) some EF code in C# to use stored procedures, and found what seems to be an anomaly (or something new to me) when finding rows matching a ...
Keith Fearnley's user avatar
4 votes
1 answer
1k views

UNION multiple MDX queries in SSAS (powerpivot)

I have some sort of difficulties trying to join 2 MDX queries together. When running them separately they work fine. The script below WITH MEMBER [Measures].[ParameterCaption] AS [...
PeddiePooh's user avatar
4 votes
2 answers
3k views

SSIS Union All does not return all the records

Very beginner question please. I have a simple script that performs a lookup for records in a different table - in this case the doctor who was assigned for treatments of a patient. Due to the lack of ...
theUnderdog's user avatar
4 votes
1 answer
4k views

How to union tables with different schema in HIVE?

I have two tables in HIVE: table A, which contains a column "N" which is of type array table B, in which column "N" does not appear both tables A and B contain column "C". I'd like to union them ...
makansij's user avatar
  • 9,623
4 votes
0 answers
2k views

How to force push predicate through UNION ALL inside a view?

I have a performance problem on a UNION ALL view. The problem can be solved by rewriting the view in two separate views but that kind of defeats the purpose of creating a view. Here is a simple test ...
jva's user avatar
  • 2,817
3 votes
2 answers
5k views

Tsql, union changes result order, union all doesn't

I know UNION removes duplicates but it changes result order even when there are no duplicates. I have two select statements, no order by statement anywhere I want union them with or without (all) i....
Li Tian Gong's user avatar

1
2 3 4 5
16