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
0 answers
77 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
1 vote
2 answers
117 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 ...
laurent's user avatar
  • 187
0 votes
0 answers
11 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
1 vote
1 answer
209 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
  • 11
2 votes
1 answer
261 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 ...
adam.g's user avatar
  • 345
0 votes
0 answers
87 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
1 answer
67 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 ...
Andrey B. Panfilov's user avatar
0 votes
0 answers
618 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
1 answer
65 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, --- --- ...
Leon's user avatar
  • 347
1 vote
3 answers
3k 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) ;...
SlowMagic's user avatar
  • 151
6 votes
1 answer
609 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
0 votes
2 answers
208 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 ...
Jean Louis's user avatar
2 votes
1 answer
128 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 ...
omerix's user avatar
  • 91
0 votes
1 answer
669 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 ...
Paul Haro's user avatar
0 votes
0 answers
98 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
1 answer
42 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 ...
PaulMcF87's user avatar
  • 167
10 votes
2 answers
8k 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
0 votes
1 answer
158 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 ...
Hasan Can Saral'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
0 votes
1 answer
438 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 ...
Tjeerd's user avatar
  • 25
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 ...
Tobi's user avatar
  • 133
1 vote
2 answers
211 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 ...
TheRealTy's user avatar
  • 213
0 votes
1 answer
430 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) ...
Martial's user avatar
  • 103
0 votes
2 answers
921 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, ...
umberto mauro's user avatar
0 votes
0 answers
124 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
1 answer
324 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, ...
Blaze's user avatar
  • 13
2 votes
1 answer
6k 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
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 ...
Jay Modi's user avatar
  • 115
0 votes
0 answers
76 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
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
  • 187
1 vote
2 answers
728 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 ...
Rakesh Poddar's user avatar
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='...
1dymium's user avatar
0 votes
1 answer
318 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)) ,...
duynguyen's user avatar
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]...
HeyJude's user avatar
  • 447
0 votes
1 answer
70 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-...
anishjp's user avatar
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
2k views

Postgresql Union: Do not repeat based on 1 column only

UNION filters out duplicate entries while UNION ALL keeps duplicate. Similar to that scenario, Consider this anological case, I want to set union to check only single column duplicacy. SELECT id, 1 AS ...
PaxPrz's user avatar
  • 219
1 vote
1 answer
591 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
0 votes
1 answer
74 views

Ignore previous rows in the UNION based on key

I have a table with task delivery times, which is based on the task department (only), task category (only), or product task and category. These rules can be applied directly to a customer, a group of ...
Tom's user avatar
  • 428
3 votes
2 answers
3k 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
0 votes
1 answer
476 views

is it relatively safe to union two tables with UUID PK? [closed]

I want to union two tables whose primary keys are UUID(V4) and I want to preserve them in the result. I work with PostgreSQL Is it safe to do this? What are the pitfalls may be encountered?
Dmitry Shvetsov's user avatar
1 vote
2 answers
3k views

Get rows above and below a certain row, based on two criteria SQL (with SUM)

Say I have a table like so: +----------+---------+------+---------------------+ |student_no|level_id |points| timestamp | +----------+---------+------+---------------------+ | 4 | ...
bb25's user avatar
  • 45
2 votes
2 answers
1k views

Get rows above and below (neighbouring rows) a certain row, based on two criteria SQL

Say I have a table like so: +---+-------+------+---------------------+ |id | level |score | timestamp | +---+-------+------+---------------------+ | 4 | 1 | 70 | 2021-01-14 21:50:38 | |...
bb25's user avatar
  • 45
1 vote
3 answers
77 views

Understanding comparison operators on columns

Consider this dummy_data: number 1 2 3 4 5 I want to return all the rows from dummy_data where column number is below a certain value. Came up with this: WITH dummy_data AS ( (SELECT 1 ...
Fabien Snauwaert's user avatar
1 vote
2 answers
1k 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
3 votes
1 answer
214 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
0 votes
2 answers
1k views

How to combine multiple EXEC statements into a single SELECT?

I run this every month to extract database logs, and I have to change the dates as well. DECLARE @start DATETIME SET @start = CONVERT(DATETIME, '2020-08-01 00:00'); DECLARE @end DATETIME SET @end = ...
Fandango68's user avatar
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 ...
paul23's user avatar
  • 453
2 votes
1 answer
348 views

Why does Type Resolution fail when using SELECT DISTINCT instead of SELECT on PostgreSQL?

Here is a minimal reproduction: SELECT 1 UNION ALL SELECT DISTINCT NULL Fails with: UNION types numeric and text cannot be matched According to the PostgreSQL documentation on Type Resolution for ...
soc's user avatar
  • 123
2 votes
2 answers
842 views

How to order some rows one way, and the rest another way?

I am trying to sort rows from a table in PostgreSQL 10.12: CREATE TABLE example ( id serial PRIMARY KEY, val text NOT NULL, x integer NOT NULL, y integer NOT NULL ); INSERT INTO example (...
Oğuz Can Sertel's user avatar

1
2 3 4 5