Questions tagged [union]
Combines two or more query results. UNION removes duplicates; UNION ALL retains all rows.
201
questions
1
vote
2answers
18 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
1answer
19 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?
-1
votes
1answer
27 views
Combine two union all using group by and 2 limit row for each group id [closed]
I need to use a faster and better performance query than the one below:
(SELECT * FROM ((SELECT * FROM index_trade_summaries WHERE index_id = 1)
UNION ALL
(SELECT * FROM ...
-1
votes
2answers
57 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 | ...
1
vote
2answers
61 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 |
|...
1
vote
3answers
39 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 ...
1
vote
2answers
28 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 ...
-5
votes
1answer
41 views
What does UNION do? Difference between Union and Union ALL? [closed]
I need some help to understand this question with some examples please never done them before in database.
What does UNION do?
Difference between Union and Union All?
3
votes
1answer
66 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 ...
0
votes
2answers
32 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 = ...
1
vote
4answers
189 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 ...
2
votes
1answer
68 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 ...
2
votes
2answers
27 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 (...
0
votes
0answers
24 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,
(...
0
votes
0answers
20 views
Save multiple dynamic filter for aggregation in Postgresql
What is the most efficient way to store multiple dynamic filters which can be created by users on given data and us the filter for aggregating data?
Lets assume I have a table called t_filter which ...
0
votes
0answers
15 views
Single table with many null fields or two tables used with union? [duplicate]
I am designing database for my small project and I've hit a "good design practice" wall ;)
I need to add records that will have one of two structures, containing different set of information (10-20 ...
0
votes
1answer
65 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 (...
0
votes
1answer
576 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 (
...
0
votes
1answer
101 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 ...
1
vote
2answers
133 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 ...
1
vote
1answer
27 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 ...
0
votes
0answers
31 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 ...
0
votes
1answer
43 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
0answers
29 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
...
0
votes
1answer
192 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 ...
0
votes
1answer
28 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 | ...
9
votes
2answers
263 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....
1
vote
2answers
309 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 ...
2
votes
3answers
2k 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 ...
1
vote
1answer
117 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.
...
1
vote
1answer
41 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 ...
0
votes
1answer
68 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 ...
0
votes
2answers
241 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 ...
1
vote
1answer
7k views
Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to float
I created some select statements which run without any problem in SQL Server. Then I joined them together with UNION and got this error message>
Msg 8114, Level 16, State 5, Line 1 Error ...
0
votes
0answers
113 views
Does MySQL Lazy Union really work?
Following query is an example of Lazy Union:
SELECT GREATEST(@found := -1, userId) AS userId
FROM user WHERE userId = 1
UNION ALL
SELECT userId
FROM user WHERE userNumber = 1 AND @found IS NULL
UNION ...
-2
votes
1answer
4k views
syntax error at or near “AS” [closed]
I have a program that takes in one or more than one table names, from a list stored in a reference table and runs a SELECT query on it. so I retrieve the table names and insert it into the SELECT ...
3
votes
2answers
36 views
Optimizing PostgreSQL where clause with OR
I have a query that uses an or in the where clause. It is slow. It seems that re-writing the query using a union could get the same results much quicker. But I'm in an environment where we generate ...
1
vote
1answer
121 views
Get the combined row count by month from 3 tables
Using Postgres 10+, I have three tables with similar structures, where I'm trying to get a count of rows grouped by months. I have a functional query working for two tables, as below:
CREATE TABLE ...
0
votes
1answer
831 views
Will too many tables slow Mysql down?
I'm making a database which should be able to handle over 1,000,000,000 rows.
in order to optimize it's performance i've decided to split tables.
so instead of having 1,000,000,000 entries in one ...
-3
votes
1answer
62 views
Print Two Different Columns with Union All- SQL
I have an SQL statement like this,
SELECT *
FROM (select "John" as FirstName Union All select "Mary" Union All select "Alis"),
(select "Doe" as LastName union all select "Queen" union all ...
0
votes
0answers
20 views
Combine multiple queries that each require a row of input values
I'm looking for a way to combine multiple queries that each need two values.
For example, I can look up a patient's age and weight as follows:
select age
, weight
from patients
join ...
0
votes
1answer
378 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
1answer
48 views
Handle 4 tables and to show 4 columns as result
i can't figure out how to build the correct query that involves 4 tables and shows these 4 columns:
I want to show this 4 rows, each from a different table.
EDIT: Each company belongs to a Sector. ...
1
vote
2answers
118 views
MySQL Select Union; Filter by Shared Relation?
Currently, I have 3 tables (see structure below), speakers, venues, and events. I'm trying to do a relevance search on the names of the speakers and venues. However, on the Search Results page, if the ...
0
votes
2answers
42 views
MYSQL search for each name in 2 columns and add values associated for each player
I have a database "game_results".
It contains the following:
PlayerID (int not null primary key auto increment)
player1 (varchar)
p1destroyed (int)
player2 (varchar)
p2destroyed (int)
winner (...
2
votes
1answer
643 views
Backend Table/Row Locking Structure using CTE Queries
This question is an extension of a previous question that I asked. Table Locking When using Unions
With the same query we are using that is causing blocking the various queries used in these UNIONS ...
2
votes
1answer
565 views
Table Locking When using Unions
When multiple selects are used in a UNION/UNION ALL are the table locks for each individual SELECT statement handled individually or are all table locks held until all SELECT statements are completed? ...
1
vote
2answers
62 views
Remove multiple joins to same table with where
I am stucked at below situation. Need expert advice.
I have 3 tables, login_details,deleted_login_details and custom_fields as below.
login_details structure = deleted_login_details
custom-fields have ...
1
vote
1answer
228 views
Oracle Subselect Union Multiple Column
I want to select and count all cats and dogs at one location;
location | cats | dogs |
new york | 150 | 66 |
paris | 34 | 12 |
What I've got so far:
SELECT
cats,dogs
FROM
(select ...
0
votes
1answer
34 views
MySQL, comparison between, tables with not all records common, on 2 time intervals
I want to make comparisons on the volume of sales, turnover and margin of all product sales
between 2 time intervals (2017-10-1 to 2017-10-31 ) and (2018-10-1 to 2018-10-31).
for example:
Product ...
