Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [union]

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

Filter by
Sorted by
Tagged with
0 votes
1 answer
368 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 ...
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 ...
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 ...
10 votes
2 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 ...
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 ...
1 vote
1 answer
625 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 ...
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 | ...
0 votes
0 answers
108 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 ...
1 vote
2 answers
301 views

Why is Postgres so slow to order those 200 rows that are already ordered?

I have two relatively complex SQL queries which I join using a UNION ALL. Each individual query is fast and returns instantly. The problem is that once joined together they perform terribly bad, and ...
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 ...
1 vote
4 answers
2k views

Get results from "combined, either inner join"

Inner join gives an "intersection" of two tables. And hence multiple inner joins give the intersection of all tables. What I--however--wish is to get an inner join that gets an intersection ...
2 votes
1 answer
262 views

Can we exchange logical disjunction for UNION ALL?

I saw in that somebody exchange below code: SELECT PK1 , PK2 , PK3 , PK4 , C , B , SUM(NUMERIC_1) , SUM(NUMERIC_2) , MAX(NUMERIC_3) , SUM(NUMERIC_4) FROM ...
0 votes
0 answers
97 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->>'...
0 votes
1 answer
78 views

May I expect PostgreSQL combines row sources in union all in the same order they are defined in query?

PostgreSQL seems to fail recognising the sorting is performed by constant and and each constant unambiguously defines corresponding row source, and as a result I observe it computes all row sources ...
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 ...
0 votes
0 answers
722 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. ...
0 votes
1 answer
66 views

Compose a complex query in Postgresql

I have a huge table like following: CREATE TABLE public.huge_table ( sampl_day date NOT NULL, tick_time timestamp(6) with time zone NOT NULL, crit_feat integer NOT NULL, --- --- ...
1 vote
3 answers
4k views

CTE with UNION ALL not working as expected

The query below seems simple and straightforward, yet it produces unexpected results. CREATE TABLE #NUMBERS ( N BIGINT ); INSERT INTO #NUMBERS VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9) ;...
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, (...
6 votes
1 answer
628 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 ...
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), ...
0 votes
2 answers
228 views

How can I optimize this SQL UNION statement?

I am using PostgreSQL 14.6 and my goal is to find out if there is simpler, and faster, way to achieve the same results but still using a similar UNION query. This is the SQL statement I would like to ...
0 votes
2 answers
986 views

UNION SQL with LEFT JOIN

I was trying to do the UNION with two Queries and i received 42601 error. Can you help me to undersant how to fixed it? CODE : SELECT Date_part('year', pr.data_chiusura) AS anno, ...
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 ...
2 votes
1 answer
129 views

Converting 2 select clauses into 1 select

I'm making a "bom" list entry into the "materials" table. I bring the materials defined for the product group to the grid so that users do not forget the relevant material. Users ...
0 votes
1 answer
745 views

Why cannot I reference the second alias from a subquery containing a UNION?

Why cannot I use Qty2 as an alias in my query? I receive this error: Error Code: 1054. Unknown column 'qty2' in 'field list' SELECT mat, qty, qty2, SUM(qty), (ifnull(SUM(qty - qty2),0)) as TotalQty ...
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 ...
0 votes
1 answer
47 views

Joining tables with Union then adding artificial Column

I Have joined 2 tables together using union as follows SELECT DateTime AS Date, Pupil, DateTime AS Time FROM tableA Where Status = 'Pending' Union SELECT DateTime AS Date, Pupil, DateTime AS Time FROM ...
0 votes
1 answer
161 views

Parallel and faster evaluation of SELECTing boolean comparing count

I have a table: CREATE TABLE IF NOT EXISTS blacklist ( id NUMERIC(20, 0) NOT NULL DEFAULT NEXTVAL('blacklist_sequence') PRIMARY KEY, device_id ...
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 ...
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 ...
-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 ...
0 votes
1 answer
470 views

How to join 2 tables and fill non matching entries with a date

A company buys parts from other companies. To not overload their servers, every 5 minutes the price of 1 part is checked to see if a part has changed price. So a table contains the last time a part ...
2 votes
1 answer
4k views

Can you paginate through 2 tables in parallel, with 1 query

I want to paginate through 2 different sources. I think I can do this using the thing where u join 2 queries into one, however, I don't want to merge them in parallel. Is it possible to select the ...
1 vote
2 answers
230 views

Locking and blocking lifetime on UNION queries on MS SQL Server

Can anyone shed any light on how locking works on UNION queries? Say I have the below query and the Archive tables take 1 min to run while the non-archive tables take 5s. Are any locks taken on the ...
0 votes
1 answer
462 views

Negative values on unsigned int with union all

When I do union all on queries with unsigned int fields, it returns negative results. Can someone explain this behavior to me ? How can I have real ids in "ticketIn" ? Schema (MySQL v5.7) ...
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 ...
0 votes
1 answer
332 views

FInd minimum and maximum number of tuples in R U πA,B ( R⋈S )

Given two relations R(A,B) with r tuples, S(B,C) with s tuples (r>0, s>0), find the min and max number of tuples in this relation: R U πA,B ( R⋈S ) Given options are: -min:r, max:r+s -min:0, ...
0 votes
0 answers
141 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 ...
0 votes
2 answers
1k views

How to sort rows after union

I have two tables that I want to combine. I need to be able to show all DelDates that had changes from the original (record 1). Record 1 as the first Record and Record 0 (table 2) always being the ...
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 ...
7 votes
4 answers
95k 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 ...
1 vote
1 answer
58 views

Returning data count from a particular day after comparing 2 tables

Here is the dbfiddle for better understanding, refer this when reading question: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b89a233c62983a23f94120eb3779a831 I have 2 tables called listings and ...
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 ...
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&...
1 vote
2 answers
830 views

Without using 'INTERSECT' , How to retrieve only the rows that exist in both tables?

I am a beginner reading the book Sams Teach Yourself SQL in 10 Minutes (Fifth Edition) to learn SQL. Here is an expmple SQL query from the book from Chapter 14 - Combining Queries. My question ...
0 votes
2 answers
59 views

What exactly does this statement do?

The statement SELECT password FROM users WHERE username='' UNION SELECT 'test' FROM users returns password -------- test and the statement SELECT username, password FROM users WHERE username='...
0 votes
1 answer
346 views

Union all causing memory overflow

I have a question about Union all I have a simple SQL to insert into table INSERT INTO tbl_a (id,userid,itemcd,amount) SELECT (nextval('seq_tbl_id')::text, 20, '0') ,'010' ,(left(serialid,3)) ,...
1 vote
1 answer
2k views

Create a json based on multiple queries on the same table when join is irrelevant

I need to construct a json based on the results of two different queries on the same table, where a join is irrelevant. Consider the following example (full demo is here): CREATE TABLE Trees ( [Id]...
0 votes
1 answer
72 views

Union All of two tables based on month of a date in Oracle [closed]

I have two tables table_a and table_b as below. column invdate is of date datatype and column amount is of number datatype. table_a invdate amount 20-01-2021 50 20-01-2021 100 20-02-2021 50 20-03-...

1
2 3 4 5