A composite key consists of multiple columns used together to uniquely identify a record. A JOIN can use multiple columns when one column alone is not sufficient to identify the correct match.
Using all required key columns prevents incorrect matches and unwanted row multiplication.
Match records using both BranchID and AccountNumber.
SELECT
a.AccountNumber,
a.BranchID,
t.TransactionAmount
FROM Accounts a
INNER JOIN Transactions t
ON a.AccountNumber = t.AccountNumber
AND a.BranchID = t.BranchID;Common in banking, financial systems, inventory systems, and databases where records are identified by multiple attributes.
- Composite keys contain multiple columns.
- JOIN conditions can contain multiple columns.
- All required key columns should be matched.
- Composite JOINs help prevent incorrect matches.
- Missing one key column can produce extra rows.
What is a composite key?
A composite key is a key made from two or more columns used together to identify a record.
Why use multiple columns in a JOIN condition?
Multiple columns may be required to uniquely identify the correct related record.