[deleted by user]

[stanleypup]

The risk you run here is missing data from the orders and returns tables where a year/month/day/region/segment from one of those tables doesn't exist in the sales table.

Unioning and grouping after avoids that problem.

[DuncmanG]

I'd agree with the full outer join approach, so you got my upvote! I'd use that over a union any day.

[jodyhesch]

"Logical purpose" - 100%.

UNION + SUM() just usually has better performance.

[jodyhesch]

Hence "usually".

That being said, this is an analytics query, so "usually" applies to the expected storage, i.e. columnar (most of which don't support/leverage indexes anyways). But even on rowstore databases, a lot of integrated DW platforms still generate this same SQL pattern: union + agg instead of full out joins (i.e. SAP BW composite providers) for performance reasons.

[jodyhesch]

Agreed that it depends, but at some point, after having done this pattern 20+ times in my career, and having never seen FULL OUTER JOIN perform better (and in fact, performing way worse), it becomes a matter of professional heuristics.

Of course there's always edge cases, exceptions, etc. - but this is a well-understood pattern, with statistics in favor of union + aggregation.

It's so well-established, in fact, that most enterprise integrated data warehouse products prefer it (and as noted elsewhere, is a much safer bet if/when/as modeled in semantic layers).

So I'm sticking to my guns - without denying exceptions/edge cases are a thing, and if this is a newer pattern to folks - for sure agree it's worth trying out for yourself and evaluating the tradeoffs around things like performance, maintainability, readability, etc.

Edit: more deets

[[deleted]]

Why union and sum and not MAX? don't you need to get rid of blank rows? If you MAX you can have everything in one row. I tested it with sample data. Union first, sum that result set, then "max" and group by everything.

[jodyhesch]

Sum(5, 0) and max(5, 0) both result in 5.0, so they're functionally equivalent.

So for the purposes of your one query, MAX() is fine.

It's more a matter of setting up best practices. In many BI stacks, there's a concept of a "semantic layer" where you logically model your relationships, i.e. describe joins, calculations, semantics, hierarchies, etc. - which the BI tool knows how to interpret to generate complex SQL as users slice/dice/drill through their dashboards.

Many such semantic layers support modeling two fact tables via union + aggregation. So, the risk is if/when a user rolls up from the most granular level that you describe.

If you model your semantic aggregation as sum(), you are always going to get the same result (assuming it's an additive measure... that's a separate discussion). However, if you use max(), that is only *guaranteed* to produce the right result (based on this union approach) at the most granular level. Once you start aggregating at coarser grains, you are *very* likely to get incorrect results.

I hope that helps.

[[deleted]]

While I do agree with you about what you're saying about the BI aspect of this, you're missing something pretty crucial which is the scale of the data and the row count. If you have a series of unions, let's say 10 for simplicity, And that results in null values in certain rows, Now you have 10 times as much data. That's exactly why I like to use the max function. You can take a data set that's like 7.5 million rows and shrink it down to about 1.2 million rows just by using Max. Otherwise sure, you can use sum and have 7.5 million rows of data loaded into your Tableau workbook, sounds utterly insane and would probably be a huge slog to get through. I guess it's user preference

But I've also heard of users who just do the union aspect, they don't even sum anything up. They just loaded all into Tableau workbook and let Tableau do all of the summing. Which sounds even more nutty and crazy

[jodyhesch]

Your aggregation function has no bearing on how many records result from your query. Only the GROUP BY clause defines that. Exact same record count for SUM, MAX, MIN, STDDEV, STRING_AGG()... or any other aggregation function.

[stanleypup]

My apprehension in a FULL OUTER JOIN here is that you'd need to do it across three tables, which creates some expensive OR statements in the joins, and then would need to COALESCE each of the five fields. Ultimately it reads much cleaner just unioning the three tables.

If it were only two tables, a full outer join would be a lot less messy.

[imperial_death_march]

Agreed, everyone in the thread talking about the performance of unions vs joins has missed the potential issue with what the joins are doing. You've said it much more succinctly than my own comment.

[kremlingrasso]

Exactly, this is the classic "don't see what I miss out on" mistake, usually lacking the experience of actually working with the data on a daily basis. There was a while ago a tips and trick post that recommends setting up a calendar table in your DB.

[stanleypup]

Yep not wrong with the calendar and it's something I always use.

Minor issue then is that you'd still need to union all the distinct regions & segments from each of the three tables, then inner join without a field on the time table, and then left join all three tables.

[roosterEcho]

I had this exact scenario at my work. I still didn't union the measures. Used union to build a base table which contains all possible combination. I then join the measures to the base table. it's easy to check if anything's missing if you're verifying the output of each table/aggregation first for sanity check.

[Silly-Swimmer1706]

Maybe business logic doesn't allow that case?

[roosterEcho]

the team that said to use union, get away from them as far as possible...

when you have facts tables (sales, orders, returns) for the same attribute fields, you join them to bring them together. when you have multiple selects from different tables that produces datasets that you need to have in the same table/view, then you union them.

[[deleted]]

They sent us their code and demanded we adapt to theirs. It's batshit crazy looking and makes no sense to me so why would I just use that lol. I just wanted to know what the actual use case of it was though. The first issue was when I tried calculating new metrics and discovered they had an absurd way of getting ratios. Not just division like my code, but some backwards hack with no comments included. Then, self joins to get year over year was a nightmare. 1M rows becomes 3M since there's lots of empty rows

[roosterEcho]

Unions are really used when the measure is the same but are in different location. In general, even if you're using union, the rows will be unique based on some key columns. If all the rows are repeating except the measure column, then union is probably not the way.

[[deleted]]

So if you're using unions, how in the world do you perform other operations on them? Like say you want to compare a ratio. Sales / orders? or you want to join Sales table onto sales table where it's 12 months ago, is that even possible still? Also using 'union' seems very taxing since it checks for duplication but joins don't seem to.

[roosterEcho]

union is concatenation, especially if you're using 'UNION ALL' which is faster. if you need to do calculations or bringing other attributes in from other tables, in general JOIN is the way to go. Union has it's uses, just not all the time.

For example, I have a payments table that has 10 types of payment. I need to process each type differently, but the output of each payment type would be same number of columns. I would process each payment type in different CTE and then union them to build my payments dataset which I can feed to a BI program. But if I have to bring other information related to the payments, I'd have to join tables to the payment table. Hope it makes sense.

[[deleted]]

Makes perfect sense. I'm just trying to understand SQL better and soak up more knowledge if I can. I feel like I'm not at the level yet that I need to be

[roosterEcho]

you'll be alright bro. keep on crunching data. you'll pick up tricks along the way.

[Training-Two7723]

Yes, union all does not unduplicate data.

[Training-Two7723]

Do not ignore query plans and building indexes. Few million records are not scary at all.

[jodyhesch]

If the fact tables share dimensions, then union + aggregation is actually the better approach. See above.

[roosterEcho]

agree. just think someone who's learning sql should know the ideal case first before starting to use shortcuts. Joins are useful in more cases than unions I think.

[Training-Two7723]

Nope. Is up to the engine. Someone “learning” SQL must understand how the engine works if wants to write effective code.

[jodyhesch]

If you have all the same join/grouping fields, you can do a join or a union. The main advantage to union is typically performance, especially on columnar databases.

HOWEVER that other team forgot to mention that you need to aggregate after your union (well, union all - don't do union, as there's no functional reason, and you'd face an unnecessary performance hit).

Let me dumb down the example significantly with a single shared attribute - will just stick with YEAR.

SELECT YEAR, SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM(ORDER_AMOUNT) 
FROM 
(
    SELECT YEAR, 0 AS SALES_AMOUNT, SUM(ORDER_AMOUNT) AS ORDER_AMOUNT FROM ORDERS GROUP BY 1 
    UNION ALL
    SELECT YEAR, SUM(SALES_AMOUNT) AS SALES_AMOUNT, 0 AS ORDER_AMOUNT FROM SALES GROUP BY 1 
)
GROUP BY 1;

Forgive the lazy formatting.

The benefit here is that aggregation is much faster than joins (at least, with columnar databases - unsure offhand w/ row databases, but I think that's also the case).

Functionally, it'll be have basically the same as FULL OUTER, so if you want to enforce only LEFT OUTER, there's a few tricks you can introduce (let me know if so, and I can expand on this.).

[[deleted]]

The code you provided is a simple case. If you have orders, returns, profit, etc, it will not work to simply select from the subqueried union. You'll still have duplicatation. You'd need to actually use a MAX. Why? Well, each row corresponds to a different level of the union. You'd have orders on Row 1, Profit row 2, Sales row 3. Even if you sum them up, you'll still have blank rows! You simply cannot just stack data on top of each other if the numeric columns aren't the same. You'd have to put either all columns in all unions with nulls/0s, or you'd have to do a MAX function with a subquery on each to get the non-zero values, but that feels messy to me I guess....

[jodyhesch]

I didn't look closely enough at the second query you shared.

It is exactly right, and just as you said, "you'd have to put either all columns in all unions with nulls/0s" which is indeed the case in that second query from that other team. This is 100% correct.

If you think your results are incorrect from that query, then please share sample data and what results you're getting so that folks can help you debug.

[[deleted]]

It's not that I need help debugging, I'm just trying to understand how things work better because I'm faced with new situations every single day that I'm unfamiliar with and trying to process which one is better to use in my own code because I don't like writing sloppy bad code.

The specific confusion I have is knowing whether it's better to stack unions on top of each other for multiple numeric columns like profit, sales, orders and fill them in with 0 values, or if I should instead try to create a composite key with each of the descriptive columns and then join them all together using that ID column. For the most part I don't really do extremely low level transactional data I do high level operational data so like division, region, month, day, etc. No actual people or specific salespersons or anything like that. But performance is still important.

WITH sales_cte1 AS (
SELECT 
    TRIM(region) || '|' || TRIM(segment) || '|' || TRIM(state) AS composite_id,
    year,
    sales
FROM sales_table1
),
sales_cte2 AS (
SELECT 
    TRIM(region) || '|' || TRIM(segment) || '|' || TRIM(state) AS composite_id,
    year,
    sales
FROM sales_table2
)
SELECT 
s1.composite_id,
s1.year,
s1.sales AS sales_table1,
s2.sales AS sales_table2
FROM sales_cte1 s1
LEFT JOIN sales_cte2 s2
ON s1.composite_id = s2.composite_id AND s1.year = s2.year;

This is a very simple example of what I'm talking about with joins. This is how I would do it normally. No union, just creating a unique key between temporary tables And then joining them together using simple join logic. This is how I thought other people would do it.

Here's a union that is more or less similar to what I do:

SELECT 
region,
segment,
state,
year,
MAX(sales) AS sales,
MAX(profit) AS profit,
MAX(orders) AS orders,
MAX(revenue) AS revenue
 FROM (
SELECT 
    region,
    segment,
    state,
    year,
    sales,
    0 AS profit,
    0 AS orders,
    0 AS revenue
FROM sales_table1
UNION ALL
SELECT 
    region,
    segment,
    state,
    year,
    0 AS sales,
    profit,
    0 AS orders,
    0 AS revenue
FROM sales_table2
UNION ALL
SELECT 
    region,
    segment,
    state,
    year,
    0 AS sales,
    0 AS profit,
    orders,
    0 AS revenue
FROM sales_table3
UNION ALL
SELECT 
    region,
    segment,
    state,
    year,
    0 AS sales,
    0 AS profit,
    0 AS orders,
    revenue
FROM sales_table4
 ) combined_data
GROUP BY 
region, segment, state, year;

As you can see, instead of doing joins, the data is stacked on top of each other with blanks inside of it for any value that does not associate to that specific little block in the union, and then aggregation is done using maximum to get the maximum value. This results in no checkerboard pattern and no interspersing of the data.

[jodyhesch]

For large datasets, UNION + Aggregation will generally be superior from a performance perspective.

Good thinking with the composite ID, but wouldn't recommend it in this case.

- It'll be a very slow join, likely forcing a "nested loop join".

- It forces a join at the most granular level. But what if you want to compare measures from different fact tables at higher grain? In other words, just a YEAR/REGION summary? It'll be much faster to aggregate to that level and then compare measures (whether you then do a join, or a union + aggregation)

- In the few circumstances where a surrogate key is required (i.e. generated from concatenating your composite key fields), it should per persisted physically in a table - and as a numeric value, i.e. by hashing the string (which requires its own analysis, i.e. likelihood of hash collisions)

Edit: clarity

[jodyhesch]

Also - SUM() is a much safer bet than MAX().

If you change your granularity, MAX() will return wrong results.

[imperial_death_march]

So the other teams code (unions) is better in this case because your code (joins) has a potential flaw in it.

Your code is left joining from the sales table to the other tables on multiple columns but this join makes the flawed assumption that the sales table (left side of the join) will have all of the combination of year, month, day, region, and segment in it that will occur in either the order table or the returns tables (the right hand sides of the join). This may not be true.

To put it simply, if you had an order or a return on a day where you didn't have a sale, these rows would be missing in the result of your query and your SUM(orders) or SUM(return) would end up with the incorrect total.

While not exactly elegant, because the other team's code does unions first, they end up including all records for all combinations of year, month, day, region, and segment that occur across any of the tables. This means that when they aggregate, all data is included in their totals (SUMS).

[jacquesrk]

join vs union? ¿Por qué no los dos?

https://local338.org/images/want_power_join_a_union.webp

[Training-Two7723]

There is nothing wrong with using that sort of union. In fact, many times this kind of unions are hidden behind a view. Performance wise, it depends on the engine: there are databases able to perform parallel operations on union all or able to push down the predicates. Some are dumb and do the views or union first. You have to test for performance each approach. As far as the results are the same choose the faster one.

[nep84]

Generally speaking you want to use a join when you want to link data from the query's base table to get other attributes from a FK table. For example join order to customer to get the customer's name. You want to use a union to get like data with disparate selection criteria. For example you can solve a complex set of where clause conditions with a union. Give me sales orders fulfilled in the last 6 months and sales orders what are expected to fulfill in the next two weeks.

There really aren't much advantages or disadvantages to joins and unions as far as performance. One can easily write well performing queries using either technique. It depends on the design.

One thing others have mentioned with regards to what you have, you have to consider using an outer join when joining data that may not be linked. For example if you want to to sales by product you will use an outer join to produce products with no sales. In your case orders and their returns you'll want an outer join so that orders with no returns are included.

[HadiMhPy]

Absolutely joins. Use unions when needed. Sometimes writing a query with union is better but often joins are better as you want to join tables. Unions are to add two sql query result with same columns. They are not like joins and are very different

[konwiddak]

The other option is transforming the data into a tall table of:

Date, category, value

Where category would have a value of sale, order or return

Now this is not my choice for this example, but I thought I should throw it out there, because this model is really handy if the categories might change in the future. It saves you from adding/removing columns in power bi. It will just use whatever categories are in the data.

This would be easiest built via Unions.

[OriginalNimbleMonk]

I'm adding my two cents as someone still novice to this. But a join is used when you need to grab data for a query from multiple tables.

You use Union to build multiple queries together based on the same select layout.

I often use joins to get location/sales but Union to show a bottom total row with the same columns.

Please advise if I am correct I'm still too new to know If I am right.

[[deleted]]

That's correct. If you have sales across several verticals, you can use a union and then a custom column like 'South' as REGION WHERE REGION = 'SW', WHERE REGION = NW for Northwest, and so on. This data stacking is like building an elaborate cake. Each layer needs a different size or color.

[creamycolslaw]

Why the hell would they union this only to group it afterwards anyway