concatenating columns from multiple unrelated 1-row resultsets

Question

Assume I have these two queries in SQL Server 2014, both of which return one row from unrelated tables:

SELECT SUM(A) A, SUM(B) B FROM X
SELECT SUM(C) C, SUM(D) D FROM Y

I'd like to combine these queries into a single resultset that contains A, B, C, D.

What's a good way to do this within a single query, as opposed to multi-query solutions like selecting the results into and out of scalar variables?

Answer 1

Two subqueries?

SELECT Q1.A, Q1.B, Q2.C, Q2.D
FROM (SELECT SUM(A) A, SUM(B) B FROM @X) Q1(A, B)
CROSS APPLY (SELECT SUM(C) C, SUM(D) D FROM @Y) Q2(C, D);

• Martin Smith: Or CROSS JOIN of course but hardly worth a separate answer.
• Mister Magoo: @martin, yes but my fingers are used to typing cross apply, I almost never type cross join.
• Vérace: @MartinSmith - I tried to get the CROSS JOIN syntax to work with MySQL - no joy! Could you show me (us?) how that syntax would work with this problem?
• ypercubeᵀᴹ: @Vérace just replace CROSS APPLY with CROSS JOIN. (You will also need to replace Q1(A,B) with Q1 and Q2(C,D) with Q2. This is not needed in SQL-Server either but will give you error in MySQL.)
• Vérace: Seems that the "CROSS JOIN" is redundant - it can just be replaced with a comma, as per @Lennart 's answer to my other question. Thanks for your comment on my (now deleted!) answer - brain burp! Which would be more efficient - the CROSS JOIN or the SELECT NULL syntax of Lennart?

Answer 2

Here's one way:

SELECT MAX(A), MAX(B), MAX(C), MAX(D)
FROM (
 SELECT SUM(A) AS A, SUM(B) AS B, NULL AS C, NULL AS D FROM X
 UNION ALL
 SELECT NULL AS A, NULL AS B, SUM(C) AS C, SUM(D) AS D FROM Y
) AS T

You may have to cast NULL to whatever type A, B, C and D have

• Vérace: Brilliant - also has the advantage of running in MySQL - racking my brains last night as to how to get this to work. How would I do this using a CROSS JOIN in MySQL?
• Lennart - Slava Ukraini: @Vérace, something like: select A, B, C, D from ( SELECT SUM(A) as A, SUM(B) as B FROM X ) as U, ( SELECT SUM(C) as C, SUM(D) as D FROM Y ) as Vshould work I guess
• Lennart - Slava Ukraini: Works with an explicit CROSS JOIN as well: select A, B, C, D from ( SELECT SUM(A) as A, SUM(B) as B FROM X ) as U CROSS JOIN ( SELECT SUM(C) as C, SUM(D) as D FROM Y ) as V
• Vérace: Indeed - as I found out, replacing CROSS JOIN with a comma works - you've done the inverse!:-)
• Lennart - Slava Ukraini: I noticed the discussion on Mister Magoo's answer after I posted the comment :-)