Q
Problem solve Get help with specific problems with your technologies, process and projects.

Comparing one set of rows with another

I have a question about comparing one set of rows with another set of rows for equality. The rows can be in the...

same table, different tables, pointed to by different cursors or result sets. Specifically, given this tip on trees from Joe Celko: Trees in SQL.

If I have a new node with subordinate nodes in a separate table called NewNode, how do I check to see if such a node already exists in my 'real' Personnel table? In other words, what is the best way to compare one set of rows to another set of rows? Do I have to programmatically iterate through both cursors/result sets/etc., comparing column values explicitly? Or is there some SQL magic you can share?


You know, that adjacency tree stuff is way over my head, and I'm not just trying to make a joke. No offence, Joe, but nested ovals, while easy to visualize, just don't translate into SQL very easily for me. I'll take your word that the increase in efficiency is worth the effort. But advice like "To convert an adjacency list model into a nested set model, use a push down stack algorithm" makes me shudder. I have a lot of respect for people who can handle that kind of code in their stride.

Let's focus on the question "What is the best way to compare one set of rows to another set of rows?" For me, the best way to operate on sets is to use the set operators provided in SQL-92: UNION, EXCEPT, and INTERSECT.

For example, to find the rows of table1 that are not also in table2, you just "subtract out" the rows that are (in fact, in Oracle the EXCEPT operator is called MINUS) --

     select * from table1
   except  
     select * from table2

If you get no rows back, then all the rows of table1 are also in table2, which is what you wanted.

You can achieve the same effect using an old-fashioned OUTER JOIN --

     select table1.*
       from table1
 outer join table2 on commonid
      where table2.commonid is null

Again, you'd get no rows returned if all the rows of table1 are also in table2.

To ensure that table2 has no extra rows that aren't in table1, you'd also want to do the EXCEPT or OUTER JOIN in the other direction.

For More Information

  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was last published in November 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close