Home » SQL & PL/SQL » SQL & PL/SQL » Comparing Two Tables - pls help
Comparing Two Tables - pls help [message #35627] Tue, 02 October 2001 07:31 Go to next message
Prabha R
Messages: 21
Registered: October 2001
Junior Member
Hi,
I have two tables A and B like :
Table A
-------
Payee Name
Payee Number
Payee City
Payee State
Payee Zip5
Payee Zip4
Payee Zip2
Payee Phone
Payee Country Code.

Table B
-------
Payee Name
Payee Number
Payee City
Payee State
Payee Merchant Number
Payee Phone
Payee Zipcode

The fields in Table B which matches with Table A should have same contents in both the tables. I need to verify this and list out the rows which does not satisfy this criteria.

Since there are around 11 columns which is present in both A and B, if i use the foll. query:
select count(*) from A where not exists (select Payee_Name from B where A.Payee_Name= B.Payee_Name and A.Payee_Number=B.Payee_Number and etc....

I need to write 10 and statements which i think is not efficient...
Could any of you suggest me another efficent way to achieve this?

----------------------------------------------------------------------
Re: Comparing Two Tables - pls help [message #35629 is a reply to message #35627] Tue, 02 October 2001 08:07 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
That _is_ an efficient way of performing that query (assuming the appropriate indexes are in place - probably on payee_number). And you aren't writing 10 or 11 _statements_, you are putting together one query with 10 or 11 conditions in the WHERE clause - big difference.

----------------------------------------------------------------------
Re: Comparing Two Tables - pls help [message #35631 is a reply to message #35627] Tue, 02 October 2001 08:32 Go to previous message
Hans
Messages: 42
Registered: September 2000
Member
another option is to use the minus operator
 
select a_1, a_2, a_3, .. , a_n from a
minus
select b_1, b_2, b_3, .. , b_n from b
 
but remember that the minus operator eliminates duplicates.


----------------------------------------------------------------------
Previous Topic: Re: delete duplicate records
Next Topic: Re: Max Open Cursors
Goto Forum:
  


Current Time: Sat May 25 04:04:56 CDT 2024