15 votes
Accepted

Are results from UNION ALL clauses always appended in order?

There was a similar question recently in pgsql-docs mailing list, Clarify the ordering guarantees in combining queries (or lack thereof): I was trying to understand what - if any - are the guarantees ...
10 votes
Accepted

Microsoft SQL Server 2014 Nested From Query in Cross-Apply

is this a bug in SQL Server? Yes, certainly, the 1 that is returned in all rows in your final result only exists in the first row of the outer input so shouldn't even be in scope for the subsequent ...
Martin Smith's user avatar
  • 84.7k
9 votes
Accepted

Problem with union casting integer to ceiling(decimal)

Looks like a bug to me and I can confirm this puzzling behaviour in: 10.2.14-MariaDB If possible you can cast the integer value to a double: SELECT cast(20 as double) UNION SELECT null UNION SELECT ...
Lennart - Slava Ukraini's user avatar
9 votes
Accepted

How to reduce query size with many repeated UNION subqueries?

Q1. Alternative without repeating subqueries Possible. Provide a set of input values, and then attach a LATERAL subquery: SELECT DISTINCT time, item_id FROM unnest('{\\...
Erwin Brandstetter's user avatar
7 votes

Union implemented with Hash Match operator

I cannot recall seeing a hash match (union) operator in the wild so I can't speak authoritatively as to when they are better than the alternatives. It's possible to force one using the { CONCAT | HASH ...
Joe Obbish's user avatar
  • 32.2k
7 votes
Accepted

Union implemented with Hash Match operator

An example query would be great. Using a numbers table (integers 1...n, where n needs to be at least 1000 for this example): SELECT N.n % 10, SPACE(100) FROM dbo.Numbers AS N WHERE N.n BETWEEN 1 ...
Paul White's user avatar
  • 84k
7 votes

T-SQL equivalent to Python Pandas Concatenate

Something like this?: CREATE VIEW MyUnion AS SELECT a, b, c, d, CAST(NULL AS varchar(32)) AS e FROM table_1 UNION ALL SELECT a, b, c, CAST(NULL AS bigint) AS d, e FROM ...
7 votes
Accepted

CTE with UNION ALL not working as expected

It's helpful to think of common table expressions more like expressions and less like (permanent) tables. Each time you reference a common table expression, it must re-express itself. Here's a simple ...
Erik Darling's user avatar
  • 40.8k
7 votes

CTE with UNION ALL not working as expected

Other answers have explained the reason for the issue happening: basically, a CTE is just an expression that evaluates as many times as it is referenced, thus causing A to return a different value at ...
Andriy M's user avatar
  • 23k
6 votes

Combining 2 SELECT queries and printing the results in PostgreSQL

The primary solution is to add parentheses: ( -- parentheses required! SELECT dt1.name AS name1, dt1.time AS time1 , dt2.name AS name2, dt2.time AS time2 FROM departure_times dt1 JOIN ...
Erwin Brandstetter's user avatar
6 votes

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

In a nutshell, CORRESPONDING was introduced into the SQL standard to make the syntax more in the spirit of the relational model (RM). A full answer would inevitable involve a discussion about how the ...
onedaywhen's user avatar
  • 2,612
6 votes

Why is this SQL query with UNION significantly faster than the same query without?

It's typically a good idea to split up that ugly OR in to a UNION query. See: Why is an OR statement slower than UNION? The first SELECT of the UNION query should melt down to milliseconds with this ...
Erwin Brandstetter's user avatar
6 votes
Accepted

Can we exchange logical disjunction for UNION ALL?

One potentially important difference is that in the second case the literals are nvarchar and in the first case they are varchar. Ignoring that aspect however... Yes SELECT * FROM TABLE WHERE ...
Martin Smith's user avatar
  • 84.7k
5 votes
Accepted

Oracle union default sort is weird

As has already been mentioned more than once, you cannot expect rows to be in a certain order without specifying that order explicitly using the ORDER BY clause. For the problem described in your ...
Andriy M's user avatar
  • 23k
5 votes

Oracle union default sort is weird

The simple answer is.... To enforce an order in ANY resultset, not just your specific case, you have to use an order by clause. The Oracle documentation spells it out clearly: Use the ORDER BY ...
Philᵀᴹ's user avatar
  • 31.8k
5 votes

Filtering UNION ALL result is much slower than filtering each subquery

What you're asking the DB to do in Query one is: Give me ALL from table A FILTERED Give me ALL from table B FILTERED Give me ALL from table C FILTERED Give me ALL from table D FILTERED And then Union. ...
Hila DG's user avatar
  • 580
5 votes

How to find out the models and prices for all the products (of any type) produced by maker B

In a UNION/UNION ALL query, a WHERE clause filters a single sub-SELECT (the one immediately preceding it) rather than the entire UNIONed set – thus, each sub-select can have its own WHERE clause. (...
Andriy M's user avatar
  • 23k
5 votes
Accepted

Howto combine UNION with EXISTS?

The WHERE clause is specific to each individual SELECT so you would need to move the one you have to the first SELECT like this: SELECT PubKey, Title FROM Publication WHERE EXISTS ( SELECT * ...
Mr.Brownstone's user avatar
5 votes

Union implemented with Hash Match operator

The UNION operator combines all the rows from one query with all the rows from another, eliminating duplicates, and forming a single list. The hash operator builds a hash table from the upper input ...
Michael Green's user avatar
5 votes
Accepted

Is a UNION query equivalent with or without DISTINCT in subqueries?

No, they are not necessarily "treated the same by the database engine." A test below shows that you might get different query plans. In many cases, the difference between query plans may not matter ...
Geoff Patterson's user avatar
5 votes

Problem with union casting integer to ceiling(decimal)

Bug MDEV-15999 Bug MDEV-15999 filed by dbdemon reported this. It's since been fixed in 10.3.1. Weird MySQL/MariaDB nature From the docs, The column names from the first SELECT statement are used as ...
Evan Carroll's user avatar
  • 63.1k
5 votes
Accepted

Check if two tables have the same structure (are compatible)

Having example schema like: create table table1 ( id integer, txt text, col1 integer, col2 integer); create table table2 ( id integer, txt text, col1 text, colx integer); and ...
bigfoot's user avatar
  • 116
5 votes
Accepted

Simplifying the "repetitive" code of a query involving UNION ALL, WHERE and LIMIT

You can use the ROW_NUMBER() analytical function to generate row numbers for each partition of (points, type): SELECT *, ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum ...
Andriy M's user avatar
  • 23k
5 votes

Microsoft SQL Server 2014 Nested From Query in Cross-Apply

This is a bug in the way SQL Server decides whether a rebind is required with certain plan shapes. It has been in the product since SQL Server 2005. Minimal repro This uses a trace flag to force a ...
Paul White's user avatar
  • 84k
5 votes
Accepted

Get results from "combined, either inner join"

Venn diagrams are not ideal to visualize join operations like a_horse commented: Venn diagrams do not visualize joins, but set operations like union, intersect or except. And his link to illustrate: ...
Erwin Brandstetter's user avatar
4 votes

Using COLLATE with UNION

Collations can be a bit tricky, especially for VARCHAR data. For both NVARCHAR and VARCHAR data, Collations control sorting and comparison rules. But for VARCHAR data, Collations also control the ...
Solomon Rutzky's user avatar
4 votes
Accepted

Vertica UNION and ORDER BY

The query needs ORDER BY in the end, not in the subqueries (and note that the parentheses are optional there, at least in standard SQL, not sure if Vertica requires them): ( SELECT country_id, ...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Syntax problem in union query

You want to group by again the result of the union. For that you need to enclose the union in parentheses and add an alias ("name" it), i.e. make it a derived table: select datum, sum(resColA) as ...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Insert characters into entire row and union with continue of row_number

You could use GROUP BY GROUPING SETS and the GROUPING() function: SELECT idx = ROW_NUMBER() OVER (ORDER BY GROUPING(a_id), a_id), a = CASE WHEN GROUPING(a_id) = 0 THEN a_id ELSE 'Total' END, ...
ypercubeᵀᴹ's user avatar
4 votes

Select all columns with same name from different tables

Try this: --Setup demo data declare @T1 TABLE (ACTIVE CHAR(1), ID INT, OTHER VARCHAR(5)) declare @T2 TABLE (ACTIVE CHAR(1), ID INT, OTHER VARCHAR(5)) declare @T3 TABLE (ACTIVE CHAR(1), ID INT, OTHER ...
Scott Hodgin - Retired's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible