The Weird and The Wonderful
The Weird and The Wonderful forum is a place to post Coding Horrors,
Worst Practices, and the occasional flash of brilliance.
We all come across code that simply boggles the mind. Lazy kludges, embarrassing mistakes, horrid
workarounds and developers just not quite getting it. And then somedays we come across - or write -
the truly sublime.
Post your Best, your worst, and your most interesting. But please - no
programming questions . This forum is purely for amusement and discussions on code snippets. All
actual programming questions will be removed.
|You'd think so. Except in SQL
We had a query:
which returned, say, 500,000 records.
Next we added
Left Join TableTwo On TableTwo.TableTwoID = TableOne.TableTwoID
Where TableTwo.TableTwoID is null or TableTwo.StringColumn <> 'value'
We're trying to find the number of records in
TableOne which, when joined with
TableTwo, either have no corresponding TableTwo row or the corresponding
TableTwo row is not 'value'.
TableTwo.StringColumn is nullable.
The result? Adding the join resulted in 25K records. It should have been over 490K records.
TableTwo.StringColumn <> 'value'
This comparison returns false if
TableTwo.StringColumn is null. So one needs to use
IsNull(TableTwo.StringColumn, '') <> 'value'
to get the correct result.
General News Suggestion Question Bug Answer Joke Praise Rant Admin
Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.