Questions tagged [union]

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

Filter by
Sorted by
Tagged with
-2 votes
1 answer
41 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 ...
user avatar
0 votes
1 answer
45 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 ...
user avatar
  • 25
1 vote
1 answer
69 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 ...
user avatar
  • 123
1 vote
2 answers
61 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 ...
user avatar
  • 213
0 votes
1 answer
29 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) ...
user avatar
  • 103
0 votes
2 answers
59 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, ...
user avatar
0 votes
0 answers
30 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 ...
user avatar
0 votes
1 answer
75 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, ...
user avatar
  • 13
0 votes
1 answer
479 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 ...
user avatar
1 vote
1 answer
51 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 ...
user avatar
  • 115
0 votes
0 answers
52 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 ...
user avatar
2 votes
2 answers
190 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&...
user avatar
  • 123
1 vote
2 answers
74 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 ...
user avatar
0 votes
2 answers
37 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='...
user avatar
0 votes
1 answer
45 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)) ,...
user avatar
0 votes
0 answers
21 views

Union based on string of identifiers Postgresql

I asked this question on the GIS Stackexchange first, but it seems the problem is strictly database related, geometry or not. I assume it would work the same if instead of joining geometry (ST_Union), ...
user avatar
1 vote
1 answer
135 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]...
user avatar
  • 331
0 votes
1 answer
22 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-...
user avatar
0 votes
2 answers
155 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 ...
user avatar
0 votes
1 answer
40 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 ...
user avatar
  • 209
1 vote
1 answer
55 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 ...
user avatar
  • 209
0 votes
1 answer
30 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 ...
user avatar
  • 285
2 votes
2 answers
37 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 ...
user avatar
0 votes
1 answer
56 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?
user avatar
-1 votes
2 answers
302 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 | ...
user avatar
  • 15
1 vote
2 answers
145 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 | |...
user avatar
  • 15
1 vote
3 answers
43 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 ...
user avatar
1 vote
2 answers
134 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 ...
user avatar
3 votes
1 answer
108 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 ...
user avatar
  • 163
0 votes
2 answers
282 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 = ...
user avatar
1 vote
4 answers
697 views

Get results from "combined, either inner join"

I have a feeling this must have been asked multiple times, but I just cannot find a solution, or even a questions probably because of difficulty in explaining. We all know that inner join basically ...
user avatar
  • 391
2 votes
1 answer
153 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 ...
user avatar
  • 123
2 votes
2 answers
40 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 (...
user avatar
0 votes
0 answers
27 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, (...
user avatar
  • 123
0 votes
2 answers
321 views

Select from other table if exist

hi I want to union 2 tables but I am not sure if the table is exits this is my query: select a from table union select case when exists(select a from table1) then (select a from table1) else (...
user avatar
  • 103
1 vote
1 answer
2k views

Subqueries for JSON path where outer query is part of a UNION result in strings rather than JSON

Bear with me - the example code has bit going on and I'll do my best to explain it, below. with ENTITIES as ( select [name] as entityName from (values ('A'), ('B'), ('C')) X([name]) ), PROPERTIES as ( ...
user avatar
0 votes
1 answer
641 views

JOIN/Union Two tables, removing NULL where possible (similar to pandas concatenate)

I am wondering how to perform an operation that I guess is most akin to a concatenate if you're familiar with pandas. Basically, what I want to do seems like a UNION where records are "collapsed" in ...
user avatar
1 vote
2 answers
135 views

Union probably not right for this [closed]

I have a table witrh 3 columns: id price created_at 1 0 ts 2 5 ts 3 0 ts 4 3 ts I'd like to have a result table that contains 2 rows, like this: id ...
user avatar
  • 35
1 vote
1 answer
30 views

Caching derived dataset automatically in SQL Server

I have a view that combines multiple tables via UNION ALL, and handles some basic errors with the data. Due to the way that the errors are handled, the view is a bit slower than I would hope. Is there ...
user avatar
  • 277
0 votes
0 answers
35 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 ...
user avatar
  • 155
0 votes
1 answer
201 views

How do I join on a timestamp and include NULL entries?

I am attempting a query to compare the sum of daily accounting invoices to a daily budget. The budget is stored as one value for a month, so I divide that amount by the total number of days as in ...
user avatar
0 votes
0 answers
30 views

SQL Server - is there a way to convert OR conditions to UNION to improve performance (as part of execution plan, under the hood)? [duplicate]

Let's assume we have following query (greatly simplified, of course): SELECT * FROM MyTable Where A or B The query takes about 30 seconds to run When I rewrite the query as SELECT DISTINCT * FROM ...
user avatar
  • 101
0 votes
1 answer
486 views

Bag union operation in relational algebra

I have difficulty in understanding how does the bag union operates in relational algebra? Here, 1) has a schema (X, Y, A) 2) (Y, A) 3) (X, A) 4) (Y, A) so how will output look like after union ...
user avatar
  • 129
0 votes
1 answer
69 views

MySQL COUNT based on a condition [duplicate]

I have a MariaDB database, in which I have this table: ID | Item Name | Item replaced | ----------------------------------------- 0 | Item 1 | Replaced 1 | Item 2 | 2 | Item 3 | ...
user avatar
10 votes
2 answers
431 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....
user avatar
  • 103
1 vote
2 answers
2k views

Alternative to slow [LEFT JOIN + OR] without UNION?

I have a reasonably simple query that returns all purchases for a given user, searching by his government issued id. The document could be in two different tables. The query using only left joins ...
user avatar
2 votes
3 answers
5k views

UNION queries with different number of columns?

I have 2 queries on a table: SELECT *, 1 as query_cr_0 FROM invoices LEFT OUTER JOIN customers ON customers.id = invoices.customer_id WHERE (customers.id IN (64, 66, 63, 62, 65)) SELECT *, 5 as ...
user avatar
1 vote
1 answer
258 views

Union from Multiple Tables in many databases and group results in batches of n

First post in dba, so please be gentle. I have a project I am working through and have 4+ SQL Server databases to work with. These DBs contain 2 (or more tables) I wish to UNION the results from. ...
user avatar
1 vote
1 answer
49 views

SQL db query using UNION and Left Join

I have 6 tables (t1, t2, t3, ...,t6 in the same SQL Server 2008 R2 database. I wanted to group them into two. Group 1 (TG1) includes tables 1, 2 and 3. Group 1 (TG1) includes tables 4, 5 and six. I ...
user avatar
  • 15
0 votes
1 answer
354 views

Is it faster to split a large table into 12 rolling monthly tables & use UNION them for reports or keep large table & delete rows older than 1 year?

My co-worker wants to split a large 158M row stats table into stats_jan, stats_feb, ... and use UNION to select from them for reports. Is that standard practice and is it faster than to just use the ...
user avatar
  • 186

1
2 3 4 5