What do you do in the case of a composite primary key?
SELECT <whatever>
FROM TableA LEFT JOIN TableB
ON TableA.keyfield1 = TableB.keyfield1
AND TableA.keyfield2 = TableB.keyfield2
AND TableA.keyfield3 = TableB.keyfield3
WHERE TableB.keyfield1 IS NULL
AND TableB.keyfield2 IS NULL
AND TableB.keyfield3 IS NULL;
Also, the result from your example is not the same as what you get on Oracle
or DB2. For example, you can say:
{Oracle}
SELECT * FROM TableA MINUS SELECT * FROM TableB;
or
{DB2}
SELECT * FROM TableA EXCEPT SELECT * FROM TableB;
This not only brings back all records in TableA that are not in TableB but
it also brings back all records in TableA whose columns have different values
than what is found in TableB. I guess implicitly the tables are joined based
on the primary keys and then the rest of the columns are compared.
I need this because I have a program that synchronizes two tables by
dynamically building SQL that I execute at a later date. This process cannot
be altered. Currently I compare records in each table based on their primary
keys values to determine whether a record has been created or deleted. I then
construct the appropriate INSERT or DELETE statement. The "Frustrated Outer
Join" query you propose will help me achieve this more efficiently provided
it supports composite primary keys (original question above). The other
piece* which I am missing is getting all records in a table whose
corresponding record in another table (I.e. joined by primary key) has
different non-primary key column values. Having those records I can iterate
through each of the columns and construct the appropriate UPDATE statement
for those column values that differ. How do I achieve this last part (*)?