Questions tagged [union]
Combines two or more query results. UNION removes duplicates; UNION ALL retains all rows.
219
questions
-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 ...
- 1
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 ...
- 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 ...
- 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 ...
- 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)
...
- 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,
...
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 ...
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, ...
- 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 ...
- 73
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 ...
- 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 ...
- 75
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&...
- 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 ...
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='...
- 3
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))
,...
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), ...
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]...
- 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-...
- 1
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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
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?
- 103
-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 | ...
- 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 |
|...
- 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 ...
- 123
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 ...
- 111
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 ...
- 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 = ...
- 260
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 ...
- 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 ...
- 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 (...
- 125
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,
(...
- 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 (...
- 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 (
...
- 1,425
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 ...
- 3
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 ...
- 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 ...
- 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 ...
- 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 ...
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
...
- 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 ...
- 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 | ...
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....
- 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 ...
- 111
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 ...
- 133
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.
...
- 13
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 ...
- 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 ...
- 186