Correct Method for Place Holders in Union Queries (1 Viewer)

Tskutnik

Registered User.
Local time
Today, 08:27
Joined
Sep 15, 2012
Messages
229
GM all. Hoping you are all safe these days.

Question on the best practice for plugging a value in a Union All query

I need to Union All the following two tables:

Table 1 Has an Account's Market Value for each day
TABLE 1 FIELDS
AccountNumber
Date
MarketValue

Table 2 has an Accounts Activity for Each Day
TABLE 2 FIELDS
AccountNumber
Date
Activity

(not every day has both a Market Value and Activity, so UNION ALL is needed)

I don't know the best way to handle the missing value in each table. I'd rather not plug 0's and then add them. Seems like there must be a better way.
The goal is to get all 4 fields on the same row

I can do this but...

**************
SELECT

AccountNumber,
Date,
MarketValue,
0 as Activity
FROM TABLE 1

Union All

AccountNumber,
Date,
0 as MarketValue,
Activity
FROM TABLE 2

**********

I could Group AccountNumber and Date, and then Sum MarketValue and Activity...
but can I avoid adding the zeros and then summing those fields?
 

Tskutnik

Registered User.
Local time
Today, 08:27
Joined
Sep 15, 2012
Messages
229
OK Thanks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 19, 2013
Messages
16,555
(not every day has both a Market Value and Activity, so UNION ALL is needed )
not necessarily. if there is a market value for every day but not necessarily activity you can left join the two tables. Or if you have another table listing all the accounts you can left join that table to both those tables - that will get you your 4 columns without the need to use a crosstab
 

Tskutnik

Registered User.
Local time
Today, 08:27
Joined
Sep 15, 2012
Messages
229
CJ - good idea on the accounts table with a left join.
Do additional joins (especially a left or right join) like this slow the database down (assuming database is optimized). As a newbie I always thought 1) the fewer tables/queries I include in each query the faster they run (again assuming optimized DB) 2) that one sided joins can be slow...

But I fully admit - I know almost nothing :).
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:27
Joined
Feb 19, 2013
Messages
16,555
Do additional joins (especially a left or right join) like this slow the database down
not particularly unless you put criteria against the child tables and there are a lot of nulls.

always thought 1) the fewer tables/queries I include in each query the faster they run (again assuming optimized DB) 2) that one sided joins can be slow...
no - the speed depends on how well optimised - only bring through the data required (the width) apply criteria to minimise the number of records (the height) rather than filtering. Indexing is the most significant factor.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:27
Joined
Oct 29, 2018
Messages
21,358
CJ - good idea on the accounts table with a left join.
Do additional joins (especially a left or right join) like this slow the database down (assuming database is optimized). As a newbie I always thought 1) the fewer tables/queries I include in each query the faster they run (again assuming optimized DB) 2) that one sided joins can be slow...

But I fully admit - I know almost nothing :).
Hi. If you're trying to show the market value next to an activity on the same day, then a UNION query is not what you want to use, let alone a UNION ALL. However, if you don't have matching dates in both tables and you don't have a table of dates, then you can use a UNION (not ALL) query to get a list of dates, which you can then INNER JOIN with your tables. As for speed, this approach will probably be slower than having a table, but I suspect the difference will make a big impact in the larger scheme of things, especially considering the need to maintain a table of dates.
 

Users who are viewing this thread

Top Bottom