Purpose
This SQL script compares two tables and categorizes the results into three distinct sets: objects present in the first table but not in the second, objects present in the second table but not in the first, and objects that are present in both tables.
Pattern
The query checks each table for matching records and sorts them into three groups based on their presence in either or both tables.
Parameters
- TBL1: This table contains the primary dataset.
- KEY1: This unique key identifies the object in question.
- TBL2: This table holds a secondary dataset that may contain related records
- KEY2: This unique key identifies the object in question.
Usage
In this use case, we have a table with clients and a table with transactions. We apply the SET pattern to get all client ids that made a transaction but are not in the client database, that are in the client database but are not made a transactions and that are in the the client database and made a transaction.
Dataset
TRANSACTIONS
CLIENTS
Parameter
- TBL1 = [DQ-SET.Transactions$]
- KEY1 = CLIENT_ID
- TBL2 = [DQ-SET.Clients$]
- KEY2 = CLIENT_ID
Statement
Output
