sql minus

J

John Vinson

Is there an equivalent? If not, what are suggested workarounds?

A "Frustrated Outer Join" query:

SELECT <whatever>
FROM TableA LEFT JOIN TableB
ON TableA.keyfield = TableB.keyfield
WHERE TableB.keyfield IS NULL;

will return records in TableA which do not have a match in tableB,
matching on keyfield.

John W. Vinson[MVP]
 
G

Guest

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 (*)?
 
J

John Vinson

The "Frustrated Outer
Join" query you propose will help me achieve this more efficiently provided
it supports composite primary keys (original question above).

It does.

Sorry I can't help on the rest!

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Minus" keyword 3
minus doesn't work !!! 2
Year Minus One 4
Remove Minus 1
Implementing Oracle's MINUS set operator in T-SQL 1
Calculation does not show minus values 2
ADO Minus/Intersect? 3
Count Problem 3

Top