13
votes
Accepted
Can I combine the results from multiple columns into a single column without UNION?
It is unclear to me what is a "more elegant way".
Oracle you can use the following statement to make columns to rows
select all_name
from foo
unpivot (all_name for col_name in (
his_name,
...
- 7,652
13
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 ...
12
votes
Can I combine the results from multiple columns into a single column without UNION?
USING UNION
Others have submitted answers trying aggregation to collect data without using UNION
May 12, 2014 : Query improvements without UNION
May 05, 2015 : Calculating values from three related ...
- 181k
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 ...
- 83.1k
9
votes
Does PostgreSQL benefit from more cores or faster cores for READ heavy applications?
Memory related settings
You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching. Just make sure you've set appropriately high values for ...
- 2,377
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 ...
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('{\\...
- 170k
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 ...
- 31.8k
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 ...
- 82.1k
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 ...
- 38.2k
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 ...
- 22.7k
6
votes
UNION query question
Sum after you did the union (hope DB2 does it this way):
SELECT BRAND, BRAND_GROUP, SUB_BRAND, SUM(INCOME) AS TOTAL_INCOME
FROM (SELECT BRAND, BRAND_GROUP, SUB_BRAND, INCOME
FROM "tema"...
- 3,700
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 ...
- 170k
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 ...
- 2,612
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 ...
- 83.1k
5
votes
Accepted
Most efficient way to generate a diff
Here is another approach:
SELECT
di.name,
di.date,
x.field,
x.oldValue,
x.newValue
FROM
@diffInput AS di
LEFT JOIN dbo.myTable AS mt ON
mt.version = @version
AND mt.name = di....
- 22.7k
5
votes
Most efficient way to generate a diff
Edit regarding fields having different types, not just decimal.
You can try to use sql_variant type. I never used it personally, but it may be a good solution for your case. To try it just replace ...
- 4,577
5
votes
Accepted
Primary key in resulting relation from union operation in relational algebra
By definition the union of two relation contains no duplicates. So it contains at least one candidate key, the composition of all attributes. In your example (id, name) is a candidate key.
DBMS do ...
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. (...
- 22.7k
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.
...
- 580
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 ...
- 31.6k
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 ...
- 22.7k
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 ...
- 24.6k
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 ...
- 8,307
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 ...
- 61.4k
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 ...
- 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
...
- 22.7k
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 ...
- 82.1k
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:
...
- 170k
Only top scored, non community-wiki answers of a minimum length are eligible