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
     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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.