Subject | Left Join vs Not Exists |
---|---|
Author | Rick Debay |
Post date | 2005-10-12T17:58:04Z |
I know of two ways to get records from set A that don't exist in set B.
The first is to do a left join, and filter where B.nonNullValue is null.
The other way would be to not do a join, and filter where there are no
results based on a query similar to the previous join statement.
What are the merits of each approach, from a best practices standpoint
and from a Firebird specific implementation? Does the appropriate
choice rest on respective set sizes, and/or the size of their
intersection?
Using Not Exists has the advantage of explicitly stating your intention,
but is there implementation specific issues with the target platform
executing a select for every item in set A? Would this make the Join
solution better for certain sets?
Rick DeBay
The first is to do a left join, and filter where B.nonNullValue is null.
The other way would be to not do a join, and filter where there are no
results based on a query similar to the previous join statement.
What are the merits of each approach, from a best practices standpoint
and from a Firebird specific implementation? Does the appropriate
choice rest on respective set sizes, and/or the size of their
intersection?
Using Not Exists has the advantage of explicitly stating your intention,
but is there implementation specific issues with the target platform
executing a select for every item in set A? Would this make the Join
solution better for certain sets?
Rick DeBay