Hidden Pitfalls with INNER JOIN and NOT IN Operators

Question

Post reply

Hidden Pitfalls with INNER JOIN and NOT IN Operators

MMartin1

One Orange Chip

Points: 27768
More actions
May 19, 2021 at 12:00 am

#3875979

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

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

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

Hugh Mungus Newbie Points: 3 More actions · Answer 1

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

DeronDilger Valued Member Points: 55 More actions · Answer 2

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 2 years, 11 months ago by DeronDilger. Reason: typo

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

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)

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 4

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

MMartin1 One Orange Chip Points: 27768 More actions · Answer 5

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 2 years, 11 months ago by MMartin1.

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

John N Hick Ten Centuries Points: 1320 More actions · Answer 6

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

MMartin1 One Orange Chip Points: 27768 More actions · Answer 7

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.

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