Questions tagged [union]
Use this tag only for questions about UNION, a keyword of the SQL language for combining the results of multiple SQL queries. Use [union-all] for the UNION-ALL keyword. Use the tag [unions] for unions in C, C++, and similar languages.
5,312
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
330
votes
14
answers
582k
views
What is the difference between JOIN and UNION?
What is the difference between JOIN and UNION? Can I have an example?
- 61.6k
299
votes
12
answers
588k
views
How to order by with union in SQL?
Is it possible to order when the data is come from many select and union it together? Such as
Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like &...
- 3,811
199
votes
3
answers
145k
views
How can I get the intersection, union, and subset of arrays in Ruby?
I want to create different methods for a class called Multiset.
I have all the required methods, but I'm unsure of how to write intersection, union, and subset methods.
For intersection and union, ...
- 2,175
167
votes
24
answers
369k
views
Intersection and union of ArrayLists in Java
Are there any methods to do so? I was looking but couldn't find any.
Another question: I need these methods so I can filter files.
Some are AND filters and some are OR filters (like in set theory), ...
- 5,394
166
votes
14
answers
233k
views
Using union and order by clause in mysql
I want to use order by with union in mysql query.
I am fetching different types of record based on different criteria from a table based on distance for a search on my site.
The first select query ...
- 1,725
140
votes
6
answers
159k
views
How can I merge two maps in go?
I have a recursive function that creates objects representing file paths (the keys are paths and the values are info about the file). It's recursive as it's only meant to handle files, so if a ...
- 6,094
110
votes
3
answers
121k
views
Combining UNION and LIMIT operations in MySQL query
I have a Jobs and a Companies table, and I want to extract 20 jobs that meet the following criteria:
Jobs only from two (2) named companies
There can at most be 10 jobs per company
I have tried ...
user172517
109
votes
17
answers
101k
views
ActiveRecord Query Union
I've written a couple of complex queries (at least to me) with Ruby on Rail's query interface:
watched_news_posts = Post.joins(:news => :watched).where(:watched => {:user_id => id})
...
- 10.1k
96
votes
11
answers
62k
views
How do I combine complex polygons?
Given two polygons:
POLYGON((1 0, 1 8, 6 4, 1 0))
POLYGON((4 1, 3 5, 4 9, 9 5, 4 1),(4 5, 5 7, 6 7, 4 4, 4 5))
How can I calculate the union (combined polygon)?
Dave's example uses SQL server to ...
- 31.9k
91
votes
5
answers
150k
views
Simplest way to form a union of two lists
What is the easiest way to compare the elements of two lists say A and B with one another, and add the elements which are present in B to A only if they are not present in A?
To illustrate,
Take list ...
- 2,142
88
votes
6
answers
67k
views
SQL Performance UNION vs OR
I just read part of an optimization article and segfaulted on the following statement:
When using SQL replace statements using OR with a UNION:
select username from users where company = ‘bbc’ or ...
- 72.4k
87
votes
16
answers
192k
views
SQL Query - Using Order By in UNION
How can one programmatically sort a union query when pulling data from two tables? For example,
SELECT table1.field1 FROM table1 ORDER BY table1.field1
UNION
SELECT table2.field1 FROM table2 ORDER BY ...
- 4,970
87
votes
22
answers
217k
views
How to perform union on two DataFrames with different amounts of columns in Spark?
I have 2 DataFrames:
I need union like this:
The unionAll function doesn't work because the number and the name of columns are different.
How can I do this?
- 882
83
votes
2
answers
26k
views
Where to use std::variant over union?
Please explain what is the difference between union and std::variant and why std::variant was introduced into the standard? In what situations should we use std::variant over the old-school union?
user7466998
78
votes
2
answers
133k
views
How do you UNION with multiple CTEs?
How do you use UNION with multiple Common Table Expressions?
I'm trying to put together some summary numbers but no matter where I put the ;, I always get an error
SELECT COUNT(*)
FROM dbo....
- 16.3k
72
votes
14
answers
327k
views
Combine two tables that have no common fields
I want to learn how to combine two db tables which have no fields in common. I've checked UNION but MSDN says :
The following are basic rules for combining the result sets of two queries by using ...
- 80.8k
72
votes
7
answers
51k
views
Incorrect usage of UNION and ORDER BY?
how can i use union and order by in mysql ?
select * from _member_facebook
inner join _member_pts
ON _member_facebook._fb_owner=_member_pts._username
where _member_facebook._promote_point = 9
...
- 12.3k
71
votes
3
answers
175k
views
Group by with union mysql select query
(SELECT COUNT(motorbike.`owner_id`) as count,owner.`name`,transport.`type` FROM transport,owner,motorbike WHERE transport.type='motobike'
AND owner.`owner_id`=motorbike.`owner_id`
AND transport.`...
- 713
70
votes
1
answer
74k
views
How to use union all in LINQ?
How to use union all in LINQ TO SQL.
I have use the following code for union, then how to use this for union all?
List<tbEmployee> lstTbEmployee = obj.tbEmployees.ToList();
List<tbEmployee2&...
- 1,421
66
votes
10
answers
154k
views
Hibernate Union alternatives
What alternatives do I have to implement a union query using hibernate? I know hibernate does not support union queries at the moment, right now the only way I see to make a union is to use a view ...
- 18.2k
65
votes
5
answers
180k
views
Using union and count(*) together in SQL query
I have a SQL query, looks something like this:
select name, count (*) from Results group by name order by name
and another, identical which loads from a archive results table, but the fields are the ...
- 17.3k
64
votes
9
answers
195k
views
UNION with WHERE clause
I'm doing a UNION of two queries on an Oracle database. Both of them have a WHERE clause. Is there a difference in the performance if I do the WHERE after UNIONing the queries compared to performing ...
- 1,609
61
votes
5
answers
175k
views
SELECT INTO USING UNION QUERY
I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn't work.
Query1: Works
SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2
Query2: ...
- 5,707
60
votes
4
answers
104k
views
Quick way to extend a set if we know elements are unique
I am performing multiple iterations of the type:
masterSet=masterSet.union(setA)
As the set grows the length of time taken to perform these operations is growing (as one would expect, I guess).
I ...
- 14.1k
60
votes
2
answers
329k
views
Combine two tables for one output
Say I have two tables:
KnownHours:
ChargeNum CategoryID Month Hours
111111 1 2/1/09 10
111111 1 3/1/09 30
111111 1 4/1/09 50
...
- 26k
59
votes
5
answers
108k
views
UNION the results of multiple stored procedures
I have a stored procedure I need to call several different times passing in different paramaters each time. I would like to collect the results as a single dataset. Is something like this possible ...
...
- 10.8k
57
votes
7
answers
182k
views
Merge two rows in SQL
Assuming I have a table containing the following information:
FK | Field1 | Field2
=====================
3 | ABC | *NULL*
3 | *NULL* | DEF
is there a way I can perform a select on the table to ...
- 751
56
votes
2
answers
46k
views
Couldn't identify equality operator of type json[] when using UNION
I'm trying to perform multiple queries on a single table using a UNION rule
I have two tables:
project (id, name, pinned BOOLEAN)
skills (m2m to projects)
I'm looking to first get an array of rows ...
- 6,416
53
votes
7
answers
125k
views
A simple way to sum a result from UNION in MySQL
I have a union of three tables (t1, t2, t3).
Each rerun exactly the same number of records, first column is id, second amount:
1 10
2 20
3 20
1 30
2 30
3 10
1 20
2 40
3 50
Is there a ...
- 53.1k
51
votes
6
answers
82k
views
How to do a count on a union query
I have the following query:
select distinct profile_id from userprofile_...
union
select distinct profile_id from productions_...
How would I get the count of the total number of results?
- 108k
49
votes
2
answers
29k
views
Combine two sql select queries (in postgres) with LIMIT statement
I've got a table and I want a query that returns the last 10 records created plus the record who's id is x.
I'm trying to do -
SELECT * FROM catalog_productimage
ORDER BY date_modified
LIMIT 10
...
- 13.2k
46
votes
4
answers
95k
views
Python set Union and set Intersection operate differently?
I'm doing some set operations in Python, and I noticed something odd..
>> set([1,2,3]) | set([2,3,4])
set([1, 2, 3, 4])
>> set().union(*[[1,2,3], [2,3,4]])
set([1, 2, 3, 4])
That's good, ...
- 4,745
41
votes
6
answers
29k
views
SQL Server UNION - What is the default ORDER BY Behaviour
If I have a few UNION Statements as a contrived example:
SELECT * FROM xxx WHERE z = 1
UNION
SELECT * FROM xxx WHERE z = 2
UNION
SELECT * FROM xxx WHERE z = 3
What is the default order by behaviour?...
- 29.4k
40
votes
2
answers
74k
views
Select from union in SQL Server
Is it possible to select from the result of a union? For example I'm trying to do something like:
SELECT A
FROM
(
SELECT A, B FROM TableA
UNION
SELECT A, B FROM TableB
)
WHERE B > 'some ...
- 1,612
39
votes
3
answers
31k
views
SQL returns less results when using UNION?
I have a SQL Server stored procedure that doesn't give me all the results when I add in the union and the second half. The first half will give me all 6 results, using the union, I only get 5.
...
- 1,359
37
votes
5
answers
192k
views
How to use group by with union in T-SQL
How can I using group by with union in T-SQL? I want to group by the first column of a result of union, I wrote the following SQL but it doesn't work. I just don't know how to reference the specified ...
- 27.7k
36
votes
3
answers
35k
views
MySQL: FULL OUTER JOIN - How do I merge one column?
I have a question regarding a FULL OUTER JOIN in MySQL. I have two (or more tables):
table1 table2
id value id value2
1 a 1 b
2 c 3 d
3 e 4 f
I have used this ...
- 363
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
35
votes
10
answers
5k
views
What's a good, generic algorithm for collapsing a set of potentially-overlapping ranges?
I have a method that gets a number of objects of this class
class Range<T>
{
public T Start;
public T End;
}
In my case T is DateTime, but lets use int for simplicity. I would like a ...
- 155k
34
votes
4
answers
107k
views
Select distinct values from multiple columns in same table
I am trying to construct a single SQL statement that returns unique, non-null values from multiple columns all located in the same table.
SELECT distinct tbl_data.code_1 FROM tbl_data
WHERE ...
- 959
34
votes
1
answer
11k
views
PostgreSQL nested CTE and UNION
I'm trying to learn SQL, using PostgreSQL 9.1.3. I would like to understand some behavior that strikes me as inconsistent. To wit:
This works:
WITH innermost AS (SELECT 2)
SELECT * FROM innermost
...
- 2,060
33
votes
3
answers
23k
views
Shallow copy of a hashset
Whats the best way of doing it?
var set2 = new HashSet<reference_type>();
Traverse the set with a foreach like this.
foreach (var n in set)
set2.Add(n);
Or use something like union like ...
- 3,263
33
votes
5
answers
60k
views
how to convert sql union to linq
I have the following Transact SQL query using a union.
I need some pointers as to how this would look in LINQ i.e some examples
wouldbe nice or if anyone can recommend a good tutorial on UNIONS in ...
- 331
33
votes
3
answers
81k
views
MYSQL UNION DISTINCT
I have two selects that I'm currently running as a UNION successfully.
(SELECT a.user_id,
a.updatecontents AS city,
b.country
FROM userprofiletemp AS a
LEFT JOIN ...
- 20.5k
32
votes
10
answers
93k
views
UNION query with codeigniter's active record pattern
How to do UNION query with PHP CodeIgniter framework's active record query format?
- 4,114
32
votes
3
answers
16k
views
How can I insert all values of one HashSet into another HashSet?
I have two HashSet<u16>s and I would like to implement a = a U b. If possible, I'd like to use HashSet::union rather than loops or other tweaks.
I tried the following:
use std::collections::...
- 3,043
32
votes
1
answer
25k
views
How to properly union with set
I understand that any python set union with empty set would result in itself. But some strange behave I detect when union is inside of a for loop.
looks good
num= set([2,3,4])
emp= set()
print num|...
- 432
29
votes
8
answers
144k
views
SQL Server: How to use UNION with two queries that BOTH have a WHERE clause?
Given:
Two queries that require filtering:
select top 2 t1.ID, t1.ReceivedDate
from Table t1
where t1.Type = 'TYPE_1'
order by t1.ReceivedDate desc
And:
select top 2 t2.ID
from Table t2
...
- 36.7k
29
votes
4
answers
8k
views
Is there a neater linq way to 'Union' a single item?
If I have two sequences and I want to process them both together, I can union them and away we go.
Now lets say I have a single item I want to process between the two sequencs. I can get it in by ...
- 51.2k