Comparing one set of rows with another

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?


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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 first published in November 2001

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top