SQL: Merging tables with composite primary keys

Data and Databases
1

I’m trying to combine two tables that have composite primary keys made up of ‘location’ and ‘timestamp’. Here’s what I’ve got:

Table A (Revenue):

location | timestamp  | revenue
1        | 2023-01-01 | 1000
2        | 2023-01-02 | 2000

Table B (Expenses):

location | timestamp  | expenses
1        | 2023-01-01 | 500
2        | 2023-01-03 | 1500

I want to merge these into one table like this:

location | timestamp  | revenue | expenses
1        | 2023-01-01 | 1000    | 500
2        | 2023-01-02 | 2000    | NULL
2        | 2023-01-03 | NULL    | 1500

I’ve tried using UNION and FULL JOIN but I can’t get it right. Here’s what I’ve attempted:

SELECT location, timestamp FROM Revenue
UNION
SELECT location, timestamp FROM Expenses

This gives me all unique location-timestamp pairs but no data.

SELECT revenue, expenses
FROM Revenue FULL JOIN Expenses
ON Revenue.timestamp = Expenses.timestamp

This joins based on timestamp only but ignores the location.

How can I properly merge these tables using both location and timestamp as the key?

2

To merge these tables correctly using both location and timestamp as the composite key, you’ll want to use a FULL OUTER JOIN. Here’s how you can do it:

SELECT COALESCE(r.location, e.location) AS location,
       COALESCE(r.timestamp, e.timestamp) AS timestamp,
       r.revenue,
       e.expenses
FROM Revenue r
FULL OUTER JOIN Expenses e
ON r.location = e.location AND r.timestamp = e.timestamp

This query will combine the tables based on both location and timestamp, ensuring all records are included even if they don’t have a match in the other table. The COALESCE function is used to handle cases where one side of the join might be NULL. This approach should give you the exact output you’re looking for, with NULLs appearing where there’s no corresponding data in one of the tables.

3

hey there! have you thought about using a CTE (Common Table Expression) for this? it might make things clearer. something like:

WITH combined AS (
SELECT location, timestamp, revenue, NULL AS expenses FROM Revenue
UNION ALL
SELECT location, timestamp, NULL, expenses FROM Expenses
)
SELECT * FROM combined
ORDER BY location, timestamp

what do you think? could this work for your case?

4

yo, try this out:

SELECT COALESCE(r.location, e.location) as location,
COALESCE(r.timestamp, e.timestamp) as timestamp,
r.revenue,
e.expenses
FROM Revenue r
FULL OUTER JOIN Expenses e
ON r.location = e.location AND r.timestamp = e.timestamp

this should do the trick for ya. it’ll merge everything nicely using both location and timestamp. lemme know if it works!