Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [union]

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

Filter by
Sorted by
Tagged with
10 votes
2 answers
9k 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 ...
Erwin Brandstetter's user avatar
8 votes
4 answers
177k views

Can I combine the results from multiple columns into a single column without UNION?

I have a table with several columns which I want to SELECT: SELECT his_name , her_name, other_name FROM foo; Bu, I instead I want to combine the results all into a single column. As an example, I ...
igx's user avatar
  • 183
4 votes
2 answers
17k views

Is there a faster way to get UNION ALL behavior in Postgres?

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
Sam's user avatar
  • 637
14 votes
3 answers
2k views

Concatenation Physical Operation: Does it guarantee order of execution?

In standard SQL, the result of a union all is not guaranteed to be in any order. So, something like: select 'A' as c union all select 'B' Could return two rows in any order (although, in practice ...
Gordon Linoff's user avatar
3 votes
2 answers
3k views

Combine results of two queries, where the second relies on the first

I have one query which is returning name(text), total, created_at And another query which is returning the same thing name(text), total, created_at The second query usually is returning more rows, ...
RockNinja's user avatar
  • 683
3 votes
2 answers
10k views

Postgres 9.2 select multiple specific rows in one query

I have a table with three columns, Id Serial. Value real. timein Timestamp. I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
Alan Cor's user avatar
  • 337
0 votes
3 answers
6k views

Oracle union default sort is weird [closed]

As you know union removes duplicated rows. I thought it removes from the latter ones, but it doesn't. I found out that if there's no order by clause, Oracle sorts the merged dataset by the first ...
Deckard's user avatar
  • 111
0 votes
1 answer
11k views

Combining 2 SELECT queries and printing the results in PostgreSQL

I have 2 queries which I want to use together to produce one output result to the user. The output describes to the user the journey to take to from A to B. I keep getting a syntax error when I try to ...
Yasser Jhan's user avatar
-1 votes
2 answers
1k views

Select multiple rows with different criteria for each [closed]

I am trying to put together a query will return sales information across multiple date ranges. I was planning to do something like this: SELECT count(*) AS thirty_day_sales FROM "MySchema"."MyTable" ...
Joe M's user avatar
  • 121
9 votes
2 answers
18k views

How to make a union view execute more efficiently?

I have a large table (tens to hundreds of millions of records) that we have split for performance reasons into active and archive tables, using a direct field mapping, and running an archive process ...
Shaul Behr's user avatar
  • 2,933
3 votes
1 answer
4k views

Best way of finding rows referencing a given id on PostgreSQL

I'm trying to find if there would be anything violating the foreign key constraint when deleting a row, to check if I can delete it or not (can't use ON DELETE CASCADE) For that, I have a piece of ...
bellini666's user avatar
3 votes
1 answer
3k views

Best practice: Unions or a derived table?

I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so: CREATE TABLE `pending` ( ... `invoice` int(11) DEFAULT NULL, `lid` int(11) DEFAULT ...
Eric L.'s user avatar
  • 153
2 votes
1 answer
10k views

MySQL - SELECT INTO OUTFILE, with a UNION

Is it possible to issue multiple select queries whose resultsets are combined via union, into the same outfile? What I am trying to do, but not working: (SELECT email FROM table WHERE createdate >...
Mike Purcell's user avatar
2 votes
1 answer
1k 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? ...
Kirk Saunders's user avatar
2 votes
1 answer
2k 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 ...
Kirk Saunders's user avatar
2 votes
2 answers
6k views

SELECT UNION from multiple tables with same schema in MySQL

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use: SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION SELECT ...
hanxue's user avatar
  • 245
1 vote
1 answer
2k 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]...
HeyJude's user avatar
  • 447
1 vote
1 answer
625 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 ...
PaxPrz's user avatar
  • 219
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 | ...
bb25's user avatar
  • 45
1 vote
0 answers
122 views

Count neighbors and sum of the neighbors values

I am looking at an edge and adjacent edges. Edge priority decreases with (1) Decrease of edge weight (2) Increase of the number of neighboring edges (3) Increase in total weight of shortest distinct ...
Stepan's user avatar
  • 213
0 votes
2 answers
1k views

What is the F301: the CORRESPONDING clause in query expression?

The SQL 2011 Spec supports a CORRESPONDING clause, Feature F301, “CORRESPONDING in query expressions”: What is this feature? How is it used? And, is it supported by any industry RDBMs?
Evan Carroll's user avatar
  • 63.6k
0 votes
1 answer
7k views

Query improvements without UNION

I have the following scenario. A users table, a records table and a memberships table. A user can be associated to zero or more records either directly with the records table records.user_id = users....
Simone Carletti's user avatar