Hidden Pitfalls with INNER JOIN and NOT IN Operators

  • Comments posted to this topic are about the item Hidden Pitfalls with INNER JOIN and NOT IN Operators

    ----------------------------------------------------

  • You mean “expected pitfalls with bad database design”.  Why are you allowing NULLs in an ID column?

  • I think the point was to simply describe a safer, simpler construct for these common scenarios.  The tables were not intended to be exact representations of best practices, but instead simple to understand and use for the examples.  Having worked with several vendor-supplied databases over the years, I run across this scenario (two-table filtering with a null-containing column) frequently.  I WISH every SQL DB out there was built "properly" or that I had the time and permission to correct shortcomings, but that's far from reality, IMO.  Articles like this help move us towards that ideal while offering a way to help protect us from the current reality.

    • This reply was modified 6 months, 2 weeks ago by  DeronDilger. Reason: typo
  • Guarding against NULLs with WHERE NOT IN should be an ingrained habit.  There certainly are alternatives with JOIN or WHERE NOT EXISTS that may or may not be more efficient, but using WHERE NOT IN without IS NOT NULL in the subquery is asking for trouble.  Unless you're sure the subquery column will NEVER contain NULL.

    WHERE abc NOT IN (SELECT xyz FROM ... WHERE xyz IS NOT NULL)
  • Scott Coleman wrote:

    Guarding against NULLs with WHERE NOT IN should be an ingrained habit.  There certainly are alternatives with JOIN or WHERE NOT EXISTS that may or may not be more efficient, but using WHERE NOT IN without IS NOT NULL in the subquery is asking for trouble.  Unless you're sure the subquery column will NEVER contain NULL.

    WHERE abc NOT IN (SELECT xyz FROM ... WHERE xyz IS NOT NULL)

    I know this is an example - but it also identifies a serious risk when using IN and NOT IN.  You should always use aliases in any subquery, but especially when used in an [NOT]IN statements.  In the above example - if the column xyz exists in the outer query but does not exist in the table(s) in the inner query it will still work, but the results will not be what you expect.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hugh Mungus wrote:

    You mean “expected pitfalls with bad database design”.  Why are you allowing NULLs in an ID column?

    Its not about my design as much as what someone might encounter. Not all fields that I have seen with the ID label are actually unique (foreign key).

    • This reply was modified 6 months, 2 weeks ago by  MMartin1.

    ----------------------------------------------------

  • @mmartin1 - Most of the flamers selectively read the article(s).  In this case, they don't understand that you were demonstrating best practices of defense against worst practices of database design.

    Good article; thanks for taking the time to author it.

  • John N Hick wrote:

    @MMartin1 - Most of the flamers selectively read the article(s).  In this case, they don't understand that you were demonstrating best practices of defense against worst practices of database design.

    Good article; thanks for taking the time to author it.

    Thank you, Glad you liked it.

    ----------------------------------------------------

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply