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.
792
questions
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?
- 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 ...
- 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 ?
- 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 ...
- 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
...
- 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 ...
- 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>
...
- 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, ...
- 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: ...
- 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 ...
- 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 ...
- 43.8k
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 ...
- 3,155
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......
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....
- 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 ...
- 105k
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 ...
- 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
...
- 202
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 ...
- 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 = ("...
- 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 ...
- 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 | ...
- 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 ...
- 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 > ...
- 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 ...
- 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.
...
- 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 ...
- 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 (...
- 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.
...
- 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`)
);
...
- 3,802
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, ...
- 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 ...
- 63
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" ...
- 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 ...
- 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
...
- 105
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 ...
- 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),
[...
- 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
...
- 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 ...
- 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 ...
- 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 ...
- 694
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 ...
- 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(...
- 19.9k
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
...
- 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
...
- 43
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 ...
- 135
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
[...
- 403
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 ...
- 105
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 ...
- 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 ...
- 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....
- 393