Questions tagged [union]

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

Filter by
Sorted by
Tagged with
1
vote
1answer
47 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 ...
0
votes
1answer
694 views

UNION or SELF JOIN?

I have a table to hold parent and child posts together. +------+----------------+--------------+-------------+------------+ | p_id | parent_post_id | child_status | post_status | post_title | ...
0
votes
0answers
47 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 ...
2
votes
2answers
98 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&...
1
vote
2answers
68 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
2answers
36 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='...
0
votes
1answer
30 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
0answers
19 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), ...
0
votes
1answer
598 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
30 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]...
0
votes
2answers
433 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 ...
0
votes
1answer
21 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-...
10
votes
2answers
370 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....
0
votes
2answers
191 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
2answers
33 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 ...
4
votes
2answers
11k views

Howto combine UNION with EXISTS?

I have a query that looks like this: SELECT PubKey, Title FROM Publication UNION SELECT NoteKey, Title, FROM Note Which works fine. My trouble start when I try to add an EXIST to it: SELECT PubKey, ...
0
votes
1answer
86 views

To find out the sum total of GRNQty and GRNValue of particular item from column 1

Query 1 # SELECT spi.Item,spi.ItemName,supdd.PackageID,spi.Specifics,iteb.brandname,supdd.Qty AS GRNQty,supdd.GP AS UnitPrice,(supdd.Qty * Supdd.GP) AS GRNValue FROM suppdeldetail AS supdd ...
1
vote
1answer
33 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 ...
0
votes
1answer
20 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 ...
0
votes
1answer
29 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 ...
2
votes
2answers
29 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
30 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
2answers
65 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
72 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 | |...
3
votes
2answers
3k views

INSERT INTO with UNION Alias ERROR

This question is derived from the following post UNION query not working Fails: INSERT INTO AssetControl SELECT * FROM CSVImport UNION SELECT * FROM AssetTemp; Works: INSERT INTO ...
1
vote
3answers
41 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
41 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 ...
3
votes
2answers
2k views

SQL Server select records from header and body table

I have 2 tables, Header and Body, with the same columns and multiple groups of records. For each group, I want one record of header and the rest of the body records. Header Id Group Name Value 1 1 ...
3
votes
1answer
85 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
142 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 = ...
0
votes
0answers
26 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, (...
2
votes
3answers
4k 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
4answers
376 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
3answers
1k views

Extracting Data from multiple archive tables

I am developing a report in report builder 3.0 where I am extracting data from a table dbo.odcalls. The problem is this table is archived on monthly basis to a separate database (HN_Backup), and I ...
2
votes
1answer
112 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
30 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 (...
1
vote
1answer
1k 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
vote
2answers
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 ...
3
votes
1answer
2k views

Unable to run union in parallel on SQL Server

I am unable to get this fairly simple query to parallelize the union operation: select va.ObjectId, 0 as IsFlag from Oav.ValueArray va where va.PropertyId = @pPropertyId ...
0
votes
1answer
407 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
1answer
29 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
32 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
139 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
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 ...
0
votes
1answer
111 views

mysql showing two select statements as one output

Hobby databasist here. In my database, the statement below will output the number of posts per hour which discussed subject foo. select count(postid) from myTable where (sub like ("%...
0
votes
1answer
342 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
34 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 | ...
1
vote
2answers
1k 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 ...
1
vote
1answer
149 views

Started using UUID's as primary key, would like to execute union of entities with BigSerial Primary Key

Some of my newer tables are now using UUID type fields as PKey and older tables are still using bigserial. I have several tables (let's call them entities) I would like to query returning the primary ...
1
vote
1answer
191 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
2 3 4 5