I have two tables same identical fields, now want to find the key columns with values match

1

i have two tables same identical fields, now want to find the key columns with values match between both tables, both tables has 1 million rows. now don't have the key column details to perform a CDC operation.

Is it possible to scan data from two table and figure account number has more matches can use acct_number as key column between two table performing the changed data comparison.

out of million if i can find 1000 common account numbers between both then i could use it as a CDC compare key column.

cust_orders_20230720
cust_orders_20230731

fields: cust_first_name, cust_last_name, cust_acct_number, cust_order_date.

thank you very much for the helpful detail.

2 
SELECT cust_acct_number FROM cust_orders_20230720
INTERSECT
SELECT cust_acct_number FROM cust_orders_20230731

That will get you the cust_acct_number's that exist in both tables. If you want to use CDC, you'll have to ensure those columns have a UNIQUE or PRIMARY KEY constraint, or a unique index.