On 15 Jul 2003 at 19:44, Marco Menardi wrote:

>Hi, is there a "clever" sql way to compare two tables?
>Put that table A and table B have this structure:
>EMPLOYER_ID, SALARY
>I want to find:
>a) if A has EMPLOYERS (records) that B misses
>b) if B has EMPLOYERS (records) that A misses
>c) if an employer in A table has a different salary than the same has
>in B table (an vice versa, but seems not necessary to check ;))

SELECT A.EMPLOYER_ID, B.EMPLOYER_ID
FROM A FULL OUTER JOIN B ON A.EMPLOYER_ID=B.EMPLOYER_ID
WHERE A.EMPLOYER_ID IS NULL OR B.EMPLOYER_ID IS NULL
OR A.SALARY<>B.SALARY
OR A.SALARY IS NULL OR B.SALARY IS NULL

The last line is not necessary is SALARY was declared as NOT NULL
or for sure doesn't have NULL values.
NULL values in the result set indicate EMPLOYER's absence in the
appropriate table. Otherwise SALARY is different.
For more comfort ORDER BY 1,2 may be added. Then different salaries
will go after (before) all missed records.

SY, Dimitry Sibiryakov.