Skip to main content

Questions tagged [union]

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

29 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
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
952 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
2 votes
0 answers
71 views

different ways to optimise union all on a particular staging process

quite often while tuning queries and stored procedures I come across the situation where I have a number of different types of the same object, each type has different characteristics but all of them ...
Marcello Miorelli's user avatar
1 vote
1 answer
632 views

Remap array columns to single column with value

Here is the sample database schema. | id | Critical | High | Low | -------------------------------------------- | 1 | {apple, ball} | {cat} | {dog, egg} | Now I want to remap these to ...
PaxPrz's user avatar
  • 219
1 vote
2 answers
2k views

Why MySQL doesn't use indexes in view with union select?

I've created a view: create view dh_hits_new_all_old as select * from dh_hits_new_201806 union all select * from dh_hits_new_201807; When I select something with a where clause containing a field ...
Tibor Nagy's user avatar
1 vote
0 answers
122 views

Count neighbors and sum of the neighbors values

I am looking at an edge and adjacent edges. Edge priority decreases with (1) Decrease of edge weight (2) Increase of the number of neighboring edges (3) Increase in total weight of shortest distinct ...
Stepan's user avatar
  • 213
1 vote
1 answer
41 views

MySQL - Query Main Table Rows and All Children from it's rows on the same query

I have the view_product which contains what i need and i have many child tables for it. Currently i have a query where i get everything in a single query (unions) and programmatically split and ...
Guilherme Ferreira's user avatar
1 vote
0 answers
793 views

Another form of WHERE NOT EXIST

I have two complex queries and I have to filter out the results from the second one, it looks something like this: SELECT STH INTO #temp_table_1 FROM TABLE1, TABLE2, TABLE3 WHERE TABLE1.ID = TABLE2....
Quentin's user avatar
  • 111
1 vote
0 answers
1k views

Multiple UNIONs get slow. Indexing or materialized views?

Let me start by stating that my DBMS is PostgreSQL 9.4. I have a database with, say 30 tables. Each table has a mirror table with historical data, that is the same columns and some timestamps. To ...
Eldamir's user avatar
  • 297
1 vote
0 answers
76 views

Does union all have an enforced ordering

I know that a union does a de-duplication so can jumble the results, but I can't find a definitive answer for union all Example query select id from ( select id from table where id = 1 union all ...
Ryaner's user avatar
  • 121
0 votes
0 answers
112 views

An alternative to PostgreSQL union?

In the following example variables in single quotes '' are arguments passed to the PostgreSQL query. If 'tag_args' is an empty array, the first row of result table is a row of three nulls and I use ...
jvkloc's user avatar
  • 133
0 votes
0 answers
13 views

st_linemerge unless property changes

My question is as follows: Given a LineShape as below, merge all elements UNLESS: Node of degree 3 or higher is present maxspeedkph property is different (50kmph -> 30kmph for example) however ...
Alex Vermeulen's user avatar
0 votes
1 answer
404 views

Union slow (4 minutes) - separate queries take 2-4 seconds

We have a linked server / db with asset data (not managed/owned by us) that we need to join with a local db (SQL Server 2019). If we run the queries separately, they all run smoothly - but when doing ...
Rendi's user avatar
  • 9
0 votes
0 answers
101 views

In PostgreSQL, UNION ALL is slow once three or more subqueries are merged

These tests were ran on PostgreSQL v15.2 I'm using UNION ALL to combine the results of various queries. The separate queries perform well: explain analyze (with params as (select foo, data->>'...
sjoene's user avatar
  • 1
0 votes
0 answers
739 views

Mismatched Union in Snowflake - dynamic approach instead of dummy columns?

As far as I understand, in snowflake, the union works as follows- Fails if there are a different number of columns. Stitches together columns based on order alone, not names. Well that's unfortunate. ...
user45867's user avatar
  • 1,683
0 votes
0 answers
105 views

Mysql UNION ALL query - Too slow using longtext

I have this query select t.field1 from ( select t1.field1, t1.field2 from db1.table t1 UNION ALL select t2.field1, t2.field2 from db2.table t2 UNION ALL select t3.field1, t3.field2 from db3.table t3 ...
Eric H's user avatar
  • 1
0 votes
0 answers
142 views

Joining two tables that have duplicate values but preserve duplicate

I need to do a specific operation on two tables which is: (A - B) U (B) B has duplicate values and A does not have any duplicate values. In the final union I want to have the duplicate values of B as ...
superflash's user avatar
0 votes
0 answers
77 views

Sanity Check - Result set different after breaking up case expression in where clause into different selects w/union all

I'm back again with more dumb questions. This time I am dealing with refactoring a single select with nested case expressions in the where clause. I'm trying to optimize this, and in attempting to do ...
scarr030's user avatar
  • 115
0 votes
2 answers
2k views

Join multiple tables without duplicated results and without UNION in PostgreSQL

Lets assume we have two tables A and B in PostgreSQL: A title first_group_id second_group_id My Title 1 2 B group_id element_name 1 First 1 Second 1 Third 2 Fourth 2 Fifth I want to get the ...
Andrei Vinogradov's user avatar
0 votes
1 answer
37 views

Select Type UNION RESULT doesn't appear in the output of Explain statement

EXPLAIN SELECT actor_id, (SELECT 1 FROM sakila.film_actor WHERE film_actor.actor_id = der_1.actor_id LIMIT 1) FROM ( SELECT actor_id FROM sakila.actor LIMIT 5 ) AS der_1 UNION ALL SELECT film_id, (...
Shylajhaa's user avatar
  • 123
0 votes
0 answers
124 views

Why does UNION fail here?

I have a problem that I can't seem to figure out how to fix. What I want to do is simple, select data and append a row of sums as the last row. But I keep getting the error An error occurred in ...
Abeer Sul's user avatar
  • 165
0 votes
0 answers
258 views

Does MySQL Lazy Union really work?

Following query is an example of Lazy Union: SELECT GREATEST(@found := -1, userId) AS userId FROM user WHERE userId = 1 UNION ALL SELECT userId FROM user WHERE userNumber = 1 AND @found IS NULL UNION ...
Rhidoy's user avatar
  • 101
0 votes
0 answers
30 views

Combine multiple queries that each require a row of input values

I'm looking for a way to combine multiple queries that each need two values. For example, I can look up a patient's age and weight as follows: select age , weight from patients join ...
nak3c's user avatar
  • 101
0 votes
1 answer
1k views

Oracle DB union two select statements which return multiple rows

I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more ...
Pulkit Mathur's user avatar
0 votes
2 answers
42 views

unmanaged data in union

Forgive me if this question already ask,not much of db guy here , here is what i tried, select row_number() over (partition by name order by challanto_date) , * from ( select rma, ...
parlad's user avatar
  • 109
0 votes
2 answers
44 views

Get table Data from single table but from different paths

A greet to all Experts here at StackExchange. The problem I have faced seems to be a simple one, it's just my low knowledge of SQL that unables me from doing it. I have a Table called '...
Mohfath's user avatar
  • 43
0 votes
2 answers
1k views

UNION or SELF JOIN?

I have a table to hold parent and child posts together. +------+----------------+--------------+-------------+------------+ | p_id | parent_post_id | child_status | post_status | post_title | ...
Dency G B's user avatar
  • 109
0 votes
1 answer
94 views

To find out the sum total of GRNQty and GRNValue of particular item from column 1

Query 1 # SELECT spi.Item,spi.ItemName,supdd.PackageID,spi.Specifics,iteb.brandname,supdd.Qty AS GRNQty,supdd.GP AS UnitPrice,(supdd.Qty * Supdd.GP) AS GRNValue FROM suppdeldetail AS supdd ...
Md Haidar Ali Khan's user avatar
-2 votes
1 answer
56 views

Print all the row with two or more element in the column

In following table how to print the student number when age occur two or more times, The result should be only column Sno i.e. Result Sno Reason 1 because 18 occur two times 2 because 20 occur ...
cool_db's user avatar