Multi-attribute Join - The join condition can involve more than one column from each input. Multi-attribute joins are commonly used for PK/FK associations between data sources involving more than one attribute on each side

multi-attribute_joins.sql

--which rows are in the production table that are not in the archive table?

SELECT
  bea.BusinessEntityId,
  bea.AddressId,
  bea.AddressTypeId,
  bea.rowguid,
  bea.ModifiedDate

FROM
  Person.BusinessEntityAddress AS bea
  
  LEFT OUTER JOIN
    Person.BusinessEntityAddressArchive as abea
  ON
    bea.BusinessEntityId = abea.BusinessEntityId AND
    bea.AddressId = abea.AddressId AND
    bea.AddressTypeId = abea.AddressTypeId

WHERE
  abea.BusinessEntityId IS NULL;