Comparing Rows for equality

bille

I have 2 data tables with the same column format, but different numbers of rows. Some of the column types are strings and others are integers.

I want to write a script that goes through table_1 a row at a time and see if there is an identical row in table_2 (based on the column elements in the row).

Do I have to compare for equality column element by element, or is there a quick way to compare the whole row? If it makes a difference, I don't need to know which table_2 row is equal, I only need to know if there is a row with equal values.

Thanks,
Bill

unknown

If you want to avoid having to run a double loop on all the rows in table A vs all the rows in table B, you could add one extra column to each, to indicate from which table all the rows in it come (i.e. 'A' or 'B'), concatenate the two tables into a single table, and then sort the combined table by one or maybe two columns that contain the largest ranges of values. Then run down the combined table row by row looking for any matches between each row and as many rows ahead as have identical values in those one or two columns. If you get a match anywhere, but find also that the extra column of those rows has an 'A' in one position and a 'B' in the other, then (and only then) compare all the other columns too, item by item. You'd then only need to search the table with a single pass (plus a few extra comparisons if you find several rows almost identical), as opposed to having to do the whole thing with a double loop.

If there is a way to compare an entire row of two tables in a single statement, I don't know what it is, but at least the above should probably speed things up a little if you're dealing with relatively large tables.