何时适合使用JOIN与UNION？多表场景下获取借款人passportId的SQL操作选择及两者差异解析

Hey there, let’s tackle these SQL questions step by step—they’re common but super important to get right!

Let’s break down the core use cases for each:

JOIN Best Scenarios #

• Combining related data across tables: Use JOIN when your tables share a common key (like passportId, user_id, or transaction_id) and you need to pull columns from multiple tables into a single row. For example:
  • Fetching a borrower's name from Primaries alongside their transaction history from Transactions
  • Linking a borrower's details to their dependents in Dependents using a shared ID
• Calculating aggregated metrics across related datasets: If you need totals, averages, or counts that depend on data from multiple tables (like total transaction value per borrower), JOIN lets you merge the data first before running aggregates.
• Working with hierarchical or linked data: JOINs are made for parent-child relationships (e.g., a primary borrower and their dependents, or products and their categories).

UNION Best Scenarios #

• Stacking similar datasets with matching columns: UNION (or UNION ALL if you don’t need to remove duplicates) is for combining rows from separate tables that have identical column structures (same data types, same order). This is ideal when:
  • You have multiple tables storing the same type of data (e.g., quarterly sales tables) and want to view them as one unified list
  • You need to compile results from separate queries that return the same column layout (like active borrowers from Primaries and borrowers who made transactions from Transactions)
• Creating a consolidated list of unique values: Use UNION to gather distinct values from multiple tables or columns (e.g., all unique borrower IDs that appear in any of your three tables).

First, let’s assume some logical table structures (aligned with typical naming conventions since you didn’t specify exact columns):

• Primaries: Main table for borrowers, with passportId as the primary key
• Dependents: Linked to Primaries via primary_passportId (the borrower’s ID)
• Transactions: Tied to borrowers via passportId (the borrower who completed the transaction)

Which to Use to Get Each Borrower’s passportId? #

It depends on your exact goal:

• If you just need all borrowers who are in the main Primaries table: You don’t need either—just run SELECT passportId FROM Primaries;
• If you need every unique borrower ID that appears in any of the three tables (borrowers in Primaries, borrowers with dependents, or borrowers with transactions): Use UNION
• If you need the borrower’s passportId alongside related data (like their dependents or transaction totals): Use JOIN

Key Differences Between JOIN and UNION for Your Tables #

JOIN Example (With Related Data)

Suppose you want each borrower’s passportId plus their total transaction amount:

SELECT p.passportId, SUM(t.amount) AS total_transaction_value
FROM Primaries p
LEFT JOIN Transactions t ON p.passportId = t.passportId
GROUP BY p.passportId;

• Combines columns from Primaries and Transactions into the same row
• Uses LEFT JOIN to include borrowers who have no transactions (they’ll show NULL for total_transaction_value)
• Result has multiple columns: passportId and total_transaction_value

UNION Example (Consolidated List)

Suppose you want every unique borrower ID across all three tables:

SELECT passportId FROM Primaries
UNION
SELECT primary_passportId AS passportId FROM Dependents
UNION
SELECT passportId FROM Transactions;

• Stacks rows from each query into a single list of passportId values
• Requires all selected columns to match in data type/order (hence the alias for primary_passportId)
• UNION automatically removes duplicate IDs (use UNION ALL if you want to keep duplicates, e.g., to count how many times a borrower appears across tables)
• Result has only one column: passportId

内容的提问来源于stack exchange，提问作者n00b