Skip to main content

Questions tagged [union]

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

Filter by
Sorted by
Tagged with
14 votes
3 answers
2k views

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 ...
Gordon Linoff's user avatar
12 votes
3 answers
43k views

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 ...
Prashant Tapase's user avatar
12 votes
3 answers
9k views

Are results from UNION ALL clauses always appended in order?

As per standard SQL UNION / UNION ALL do not guarantee any particular sort order without an outer ORDER BY clause - like there is hardly any place in SQL where sort order is guaranteed without ORDER ...
Erwin Brandstetter's user avatar
12 votes
1 answer
1k views

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 ...
Joe Obbish's user avatar
  • 32.5k
11 votes
1 answer
28k views

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 ...
Lukas's user avatar
  • 213
10 votes
2 answers
648 views

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: https://feedback.azure....
Phist0ne's user avatar
  • 103
10 votes
2 answers
2k views

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'...
ngcbassman's user avatar
10 votes
2 answers
59k views

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 ...
Anja's user avatar
  • 151
9 votes
2 answers
18k views

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 ...
Shaul Behr's user avatar
  • 2,933
8 votes
4 answers
177k views

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 ...
igx's user avatar
  • 183
8 votes
2 answers
380 views

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

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), ...
Zilk's user avatar
  • 1,121
7 votes
2 answers
18k views

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 ...
孔夫子's user avatar
  • 4,330
7 votes
4 answers
96k views

How to combine UNION and INNER JOIN?

I'm fetching the grandparent of all nodes in a tree as follows: SELECT P.id, P.parent, GP.parent gp_id FROM product_groups P INNER JOIN product_groups GP ON P.parent = GP.id There is no ...
Tim's user avatar
  • 85
6 votes
1 answer
635 views

How to reduce query size with many repeated UNION subqueries?

I use Postgres 13 and have a table defined with the following DDL: CREATE TABLE item_codes ( code bytea NOT NULL, item_id bytea NOT NULL, time ...
Vitalii Vitrenko's user avatar
6 votes
2 answers
12k views

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 ...
Anna Tomasson's user avatar
6 votes
3 answers
9k views

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

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 ...
sam.bishop's user avatar
5 votes
3 answers
5k views

Primary key in resulting relation from union operation in relational algebra

I have a question regarding the resulting table/relation from a union operation in relational algebra. Does the resulting relation not have a primary key? Or does it return a table with a hidden ...
Daniel Valland's user avatar
5 votes
2 answers
17k views

Howto combine UNION with EXISTS?

I have a query that looks like this: SELECT PubKey, Title FROM Publication UNION SELECT NoteKey, Title, FROM Note Which works fine. My trouble start when I try to add an EXIST to it: SELECT PubKey, ...
Bjørn Fridal's user avatar
4 votes
5 answers
7k views

Check if two tables have the same structure (are compatible)

I was wondering if there is an established way to test if two PostgreSQL tables/relations have the same structure. In other words, how to test whether they are compatible with each other in the sense ...
tinlyx's user avatar
  • 3,610
4 votes
2 answers
17k views

Is there a faster way to get UNION ALL behavior in Postgres?

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
Sam's user avatar
  • 637
4 votes
2 answers
10k views

Calculating average with AVG for a list of numbers

This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL like: SELECT 2+...
JamesBrown's user avatar
4 votes
2 answers
2k views

Union tables by mask in PostgreSQL [closed]

In database I have many tables like with names: "t_2015_09_01_z15" "t_2015_09_02_z15" "t_2015_09_03_z15" "t_2015_09_04_z15" "t_2015_09_05_z15" ... "t_2015_10_01_z15" "t_2015_10_02_z15" "...
spatialhast's user avatar
4 votes
1 answer
20k views

COLLATION and UNION ALL

I have the following queries: SELECT * FROM OPENQUERY([SERVERA], 'SELECT ''SERVERA'',[PhoneNum] ,[PhoneExt] ,COUNT(2) as CNT FROM [DB].[dbo].[ResourcePhone] WHERE PhoneNum like ''+...
Tom's user avatar
  • 1,549
4 votes
1 answer
4k views

Speed slow down on a UNION ALL view

This is my DB architecture: a 3.000.000 records product (memory) table that must be shared by all databases a x (say 1) records product table specific for each single database a view in each ...
Zeno's user avatar
  • 51
3 votes
2 answers
4k views

Why is this SQL query with UNION significantly faster than the same query without?

I'm trying to optimize a query, which never completes on Postgres 12.7. It takes hours, even days, make the CPU goes 100%, and never returns: SELECT "id", "counter", "item_id&...
laurent's user avatar
  • 191
3 votes
2 answers
16k views

MySQL - INSERT INTO ... SELECT ... UNION

I'm having an issue with a fairly complex query where I need to store the ids from one table (based on heavy criteria) into a narrow, alternate table for future reference, but I keep running into a ...
Mike Purcell's user avatar
3 votes
1 answer
243 views

T-SQL equivalent to Python Pandas Concatenate

I have a few tables that I would like to merge in a similar way that Python Pandas Concatenate does. I do not want to replicate the information for it is rather large. I'd like to have a VIEW like ...
Wilmar's user avatar
  • 173
3 votes
1 answer
2k views

Unable to run union in parallel on SQL Server

I am unable to get this fairly simple query to parallelize the union operation: select va.ObjectId, 0 as IsFlag from Oav.ValueArray va where va.PropertyId = @pPropertyId ...
crokusek's user avatar
  • 2,120
3 votes
2 answers
5k views

Select next or first row

For a given ID, I'm trying to get the next row or if not present (end of list), return the first one. ID (primary key) value 1 John 3 Bob 9 ...
Stéphane Bruckert's user avatar
3 votes
1 answer
2k views

How to union four different tables

I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL. I have two other tables that only uses a WHERE clause and both of these are combined using ...
deepz's user avatar
  • 267
3 votes
1 answer
7k views

UNION ALL slows down SQL request significantly

I have the problem that the following request takeing about 45 minutes to complete (Azure S2 MSSQL): SELECT * FROM fooEmployeesView fe WHERE fe.departmentId = 'xyz' UNION ALL SELECT * FROM ...
mgerbracht's user avatar
3 votes
2 answers
3k views

SQL Server select records from header and body table

I have 2 tables, Header and Body, with the same columns and multiple groups of records. For each group, I want one record of header and the rest of the body records. Header Id Group Name Value 1 1 ...
heyNow's user avatar
  • 275
3 votes
1 answer
2k views

Syntax problem in union query

I hope someone can tell me what I'm doing wrong in this query. I'm unable to find the cause of the error. I have 2 tables with 2 columns each, see the CREATE statements here: CREATE TABLE TableA(...
Ruben van Straten's user avatar
3 votes
3 answers
4k views

MySQL -Query with Union doesn't use index

The following query runs instantly: SELECT COUNT(*) FROM indicators.fileso WHERE sha256 IN ( SELECT a.sha256 FROM temp.ananifilesinfo a JOIN indicators.filesi fi ON fi.sha256 = a.sha256 ...
omri's user avatar
  • 440
3 votes
2 answers
3k views

Combine results of two queries, where the second relies on the first

I have one query which is returning name(text), total, created_at And another query which is returning the same thing name(text), total, created_at The second query usually is returning more rows, ...
RockNinja's user avatar
  • 683
3 votes
1 answer
3k views

Postgres: "Pivot" unioned table based on one column

I have a query that does this sort of thing: (SELECT rel_id, timestmap, y FROM table_1 AS full_ WHERE full_.timestamp BETWEEN %s AND %s ORDER BY full_.timestamp) UNION ALL (SELECT rel_id, timestamp,...
Steve Pike's user avatar
3 votes
1 answer
2k views

Fast query, in theory using index, still in the slow-query log

I'm having a hard time interpreting the EXPLAIN results of these queries. They both end up in the slow-query log, but the execution time is ~0.0050ms and the final result set is always under 100 rows. ...
The Disintegrator's user avatar
3 votes
2 answers
11k views

Postgres 9.2 select multiple specific rows in one query

I have a table with three columns, Id Serial. Value real. timein Timestamp. I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
Alan Cor's user avatar
  • 337
3 votes
1 answer
3k views

Best practice: Unions or a derived table?

I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so: CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT ...
Eric L.'s user avatar
  • 153
3 votes
1 answer
1k views

Optimze performance for LIMIT / OFFEST on collected result of multiple queries

I have large tables in Postgres 10.4 (many millions rows) and three piped SELECT queries. The percentage of rows contributed to the total result for each SELECT is roughly: SELECT 1 : ~5 % rows ...
Vincent's user avatar
  • 133
3 votes
2 answers
4k views

INSERT INTO with UNION Alias ERROR

This question is derived from the following post UNION query not working Fails: INSERT INTO AssetControl SELECT * FROM CSVImport UNION SELECT * FROM AssetTemp; Works: INSERT INTO ...
James Peter McConnell's user avatar
3 votes
1 answer
4k views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
bellini666's user avatar
3 votes
2 answers
60 views

Optimizing PostgreSQL where clause with OR

I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate ...
mdahlman's user avatar
  • 357
3 votes
1 answer
6k views

Fastest MySQL pagination and ORDER from a huge UNION ALL VIEW

Let's say we have a schema with table 'month' which have the current information and table 'year' which have the historic information. Each table has around 65,000 rows. This schema exists in several ...
Uriel's user avatar
  • 133
3 votes
1 answer
2k views

Selecting max(date) from union query

I am having a bit of trouble getting accurate results from a query that looks to me as if it should work. RDBMS is MySQL. I am trying to get the max last login date from a union select on three ...
amatusko's user avatar
  • 487
3 votes
1 answer
476 views

How can I conditionally summarize multiple rows in Microsoft Access?

I have a table of data, in Microsoft Access 2003, listing multiple rows for each person. I want to conditionally summarize a subset of these rows for each person, for instance, if entry for columnA = ...
AncientSwordRage's user avatar
3 votes
0 answers
2k views

How to improve performance when joining against coalesce

In an effort to remove a caching layer from our application, I'm building a number of views to encapsulate the different ways a user can have access to a piece of content - one per "rule" of access. ...
outofculture's user avatar
3 votes
1 answer
953 views

Query with conditional JOIN running slow

I have a query the first part of which looks like this - With CTE AS ( select DISTINCT A.col1, A.col2, . . . from tableA A JOIN tableB B ON ( ...
vir's user avatar
  • 41

1
2 3 4 5