Lesson 3 of 15 · Lab
Append vs join: stacking rows from two tables
Appending stacks the rows of one table under another, so you get more rows and the same columns. A join does the reverse, adding columns to the same rows. Append when the tables hold the same kind of record, such as this month's orders under last month's.
A January file of 3 orders and a February file of 2 become one table of 5 orders, still with one amount column.
Your two files here come from fictional Vandelay Coffee Supply, whose September export starts on August 25 to be safe.
10 rows and 8 rows go in, with two invoices in both files. How many come out?
18 rows. Union stacks rows and has no idea what an invoice is, so the overlap arrives without a warning. Removing duplicates deals with it later.
Stack two files, then break a column on purpose
- This live flow in Strata, Oplema's browser app for data prep, opens with Union selected. Each preview row has a
Source:input_1is the upper file,input_2the lower. - Set When columns differ to Error on mismatch. Nothing changes, because the names match.
- Select the lower Manual Input. In its table, scroll right and select the header
amount, change it toprice, then select the block on the canvas. - Select Union. It turns red: the columns differ. Change the lower input's header back to
amount, and the red clears. - Select Summarize, the last block: 10 rows and 8. Select Add, set the new Action to Sum, and open its Column list. It stays red: no column here is numeric, because
amountis text. Grouping and summarizing covers Summarize properly.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
What Union checks, and what it can't
By default, a renamed column arrives as two half-empty columns and the flow still succeeds. Error on mismatch turns that quiet damage into a stop. It only checks names, though, so two files can agree on amount and mean different things by it.
The refused sum is a clue. amount holds 1.240 and TBC, so it's still text, and Data types and casting makes it a number. See the Union reference for its settings.
Common append mistakes
- Mixed units or currencies. Euros stacked on dollars add up without complaint and mean nothing. Check units, currency and what each row represents.
- Overlapping periods. A record in both files is counted twice until you deal with it.
- Using append to add detail. To bring in a customer's region, you want a join.
UNION ALL and Excel's Append
SELECT invoice_id, amount FROM july
UNION ALL
SELECT invoice_id, amount FROM augustUNION ALL keeps every row, while plain UNION also removes rows that are identical, including real repeats. SQL matches columns by position rather than name, so list them in the same order. In Excel, Power Query's Append matches by column name, and pasting or VSTACK matches by position.