Finance wants parcel IDs that appear in both delinquency and lien datasets for targeted outreach. Which set operation fits?

Finance outreach that needs parcel IDs present in both delinquency and lien datasets is an INTERSECT (or equivalent semi-join) problem. Intersection returns the shared parcel set—the shortlist for coordinated collections or hardship programs—without pulling in unmatched parcels from a full outer join on parcel ID or limiting results to delinquency-only parcels through EXCEPT. That set operation matches the civic goal of targeting properties that appear in both risk systems.

A full outer join to link delinquency and lien rows on parcel ID for outreach fails for this task because it returns parcels from either dataset, including those present in only one source, rather than only the shared parcel IDs Finance needs. EXCEPT to return delinquency parcel IDs for follow-up based on lien records drops the overlap finance asked for because it keeps parcels in delinquency that are not in liens. UNION ALL to retain parcel IDs from delinquency and lien datasets for analysis combines both lists instead of isolating the shared parcels, so it includes parcels that appear in only one system.

Exam caveat: INTERSECT = overlap; EXCEPT = in A not B; UNION/UNION ALL = combine. Choose the set operator from the business question, not from habit. Joins can implement the same logic, but using a full outer join to link all rows when overlap is required, or preferring UNION ALL to retain every ID, is wrong.

Operational check: Build small delinquency and lien parcel sets with known overlap, run INTERSECT, and confirm only shared parcel_ids return. Show EXCEPT and UNION ALL results diverge from the outreach list. Hand the intersected set to a sample campaign query and verify each parcel exists in both source extracts.