Re: New-Bie SQL Question...

Hi BG,

Okay, you can have two situations:

1. There are ten records in table 1 that are not in table 2 and all records in table 2 are in table 1.
2. There are more than ten records in table 1 that are not in table 2 and there are n - 10 records in table 2 that are not in table 1.

The mechanics of eliminating the dups is easy, but the implications of eliminating
rows from a table that might be valid must be considered.

Mechanically you can solve the problem by altering the tables and adding a primary key to each of them. Doing so will eliminate any rows containing duplicate key values and any rows with a null key field.

You can then SELECT a.col1, a.col2, ... a.coln

                            FROM table1 a,  table2 b
                             WHERE  a.primary_key != b.priimary_key;

next You         SELECT b.col1, b.col2, ... b.coln
                            FROM table1 a,  table2 b
                             WHERE  a.primary_key != b.priimary_key;

finally compare the output of the two queries to identify all of the rows in either
table that are not in the other table.

The catch is that the process of eliminating dups may eliminate otherwise valid rows that have been entered with incorrect keys. If this a concern create indexes rather than Primary Key indexes, run the two selects, and then compare the results. There are two problems with this approach:

1. rows with null values in the key field will not be identified
2. It will take longer to run the queries.

The first problem can be resolved by selecting rows where the key is null from each of the two tables.

The second problem you just have to live with.

>
>I have 2 tables (similar with 10 fields each...)
>each one has around 500K Rows with the second one
>having 100 Rows extra....
>
>How do I find the 100 Rows which are extra in the 2nd table
>using one SQL...??
>
>TIA,
>Bg
>
>

Regards

Jerry

Jerry Gitomer Since I know how to spell DBA, I became one. Received on Fri May 08 1998 - 00:00:00 CDT