Questions tagged [union]
Combines two or more query results. UNION removes duplicates; UNION ALL retains all rows.
234 questions
0
votes
2
answers
78
views
Can This Code Be Exploited for SQL Injection with Significant Impact?
I’m currently debating with a colleague whether the following (pseudo) code is vulnerable to SQL injection (SQL Server):
database.BeginTransaction();
String userId = dto.UserId;
String firstQuery = &...
- 1
0
votes
0
answers
196
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 ...
- 133
1
vote
2
answers
1k
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 ...
- 191
0
votes
1
answer
749
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 ...
- 9
2
votes
1
answer
272
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 ...
- 421
0
votes
0
answers
163
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->>'...
- 1
0
votes
1
answer
101
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 ...
0
votes
0
answers
901
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.
...
- 1,709
0
votes
1
answer
74
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,
---
--- ...
- 359
1
vote
3
answers
5k
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)
;...
- 151
6
votes
1
answer
683
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 ...
- 185
0
votes
2
answers
343
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 ...
- 155
2
votes
1
answer
131
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 ...
- 101
0
votes
1
answer
891
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
114
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
...
- 1
0
votes
1
answer
146
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 ...
- 177
12
votes
3
answers
12k
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 ...
- 182k
0
votes
1
answer
180
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 ...
- 179
-2
votes
1
answer
59
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
535
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
3
votes
1
answer
5k
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 ...
- 143
1
vote
2
answers
274
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
490
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
1k
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
178
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
357
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
3
votes
1
answer
8k
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 ...
- 103
1
vote
1
answer
59
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
80
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 ...
- 115
3
votes
2
answers
5k
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&...
- 191
1
vote
2
answers
976
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
60
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
387
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
3k
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]...
- 457
0
votes
1
answer
89
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
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 ...
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 ...
- 219
1
vote
1
answer
713
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 ...
- 219
0
votes
1
answer
107
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 ...
- 428
3
votes
2
answers
6k
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
598
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
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 | ...
- 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 |
|...
- 45
1
vote
3
answers
98
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 ...
- 143
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 ...
- 111
3
votes
1
answer
292
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 ...
- 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 = ...
- 295
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 ...
- 483
2
votes
1
answer
404
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
1k
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