Home » SQL & PL/SQL » SQL & PL/SQL » finding common rows
finding common rows [message #251058] Thu, 12 July 2007 06:00 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
I have 2 tables, TableA and TableB. Both have the same structure with 50 columns. I want to find the common records. However, I find it sloppy to write a SQL like:

select *
  from TableA
 where (-- enumerate all 50 columns! --) in
       (select -- enumerate all 50 columns! --
          from TableB)


Is there an easier/cleaner way to write such SQL?
Re: finding common rows [message #251061 is a reply to message #251058] Thu, 12 July 2007 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If your logical key is on 50 columns then you have to check each of the 50 columns.
How can you do otherwise?

Btw, join is better than IN and take care about NULL values (if there is any).

Regards
Michel
Re: finding common rows [message #251076 is a reply to message #251061] Thu, 12 July 2007 06:29 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
or you can use INTERSECT, but you still need to list all the columns.
Re: finding common rows [message #251120 is a reply to message #251076] Thu, 12 July 2007 07:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the columns are in the same order in both tables, you can just do
SELECT * FROM table_1
INTERSECT
SELECT * FROM table_2
Previous Topic: package passing values
Next Topic: max extents
Goto Forum:
  


Current Time: Sat Dec 03 12:11:10 CST 2016

Total time taken to generate the page: 0.07201 seconds