Click here to Skip to main content

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.

Re: Does NULL <> 'string'?

Richard Deeming13-Jul-17 10:35

Richard Deeming

13-Jul-17 10:35

0x01AA wrote:
BTW: Have some Problems to seriously translate "if you're anal about compliance"

anal - Wiktionary[^] - definition #3: of a person, compulsive and stubborn, obsessed with neatness and accuracy

Synonyms: fussy, pernickety, picky

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Does NULL <> 'string'?

User 1106097913-Jul-17 10:58

13-Jul-17 10:58

Thank you for help me learning English. What such a insignifican word like "about" can change everything Laugh | :laugh:

Laugh | :laugh:

Thank you
Bruno

modified 19-Jan-21 21:04pm.

Re: Does NULL <> 'string'?

Richard Deeming11-Jul-17 9:08

Richard Deeming

11-Jul-17 9:08

Chris Maunder wrote:
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'.

Either I've misunderstood your requirement, or that query isn't technically correct.

The query will find the records in TableOne which don't have any corresponding rows in TableTwo, and the records in TableOne which have at least one corresponding record in TableTwo where StringColumn is Null or not equal to 'value'. If there are multiple matching rows in TableTwo, then the rows from TableOne will be repeated.

Based on the description, I'd have thought something like this would be a better fit:

SQL

SELECT Count(1)
FROM TableOne
WHERE Not Exists
(
    SELECT 1
    FROM TableTwo
    WHERE TableTwo.TableTwoID = TableOne.TableTwoID
    And TableTwo.StringColumn = 'value'
)

That finds all the rows in TableOne which don't have a corresponding row in TableTwo with StringColumn set to 'value'.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Does NULL <> 'string'?

Jeremy Falcon11-Jul-17 9:20

11-Jul-17 9:20

I think this version reads cleaner (and without the quirks), but I'd be willing to bet the non-sub select version would run quicker.

Jeremy Falcon

Re: Does NULL <> 'string'?

TheGreatAndPowerfulOz11-Jul-17 9:37

TheGreatAndPowerfulOz

11-Jul-17 9:37

Jeremy Falcon wrote:
would run quicker

Indeed.

#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun

Re: Does NULL <> 'string'?

Jörgen Andersson11-Jul-17 11:11

Jörgen Andersson

11-Jul-17 11:11

Assuming SQL Server, I'll take that bet.

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server at EXPLAIN EXTENDED[^]
Left outer join vs NOT EXISTS | SQL in the Wild[^]

There are always exceptions, but not exists is generally better optimized.

Wrong is evil and must be defeated. - Jeff Ello

Re: Does NULL <> 'string'?

Jeremy Falcon11-Jul-17 9:17

11-Jul-17 9:17

I feel ya man. NULL has always been a special case though. It's the bastard stepchild of values. Isn't technology fun?

Jeremy Falcon

Re: Does NULL <> 'string'?

TheGreatAndPowerfulOz11-Jul-17 9:38

TheGreatAndPowerfulOz

11-Jul-17 9:38

This proves SQL is not a real programming language.

#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun

Re: Does NULL <> 'string'?

Chris Maunder11-Jul-17 9:52

11-Jul-17 9:52

Au contraire. It's the perfect language. Job security forever.

cheers
Chris Maunder

Re: Does NULL <> 'string'?

TheGreatAndPowerfulOz11-Jul-17 10:00

TheGreatAndPowerfulOz

11-Jul-17 10:00

LOL. Especially when you have your own company! Poke tongue | ;-P

Poke tongue | ;-P

#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun

Re: Does NULL <> 'string'?

Erick Mattew11-Jul-17 10:17

11-Jul-17 10:17

Quote:
Job security forever

Indeed. Writing script, backup,... It's boring but secure.

Re: Does NULL <> 'string'?

Jeremy Falcon11-Jul-17 10:58

11-Jul-17 10:58

So is prostitution... but ya know... Unsure | :~

Unsure | :~

Jeremy Falcon

Re: Does NULL <> 'string'?

Jörgen Andersson11-Jul-17 11:20

Jörgen Andersson

11-Jul-17 11:20

Boring?

Wrong is evil and must be defeated. - Jeff Ello

Re: Does NULL <> 'string'?

Jeremy Falcon11-Jul-17 11:29

11-Jul-17 11:29

Some are... so I'm told. Roll eyes | :rolleyes:

Roll eyes | :rolleyes:

Jeremy Falcon

Re: Does NULL <> 'string'?

TheGreatAndPowerfulOz11-Jul-17 12:33

TheGreatAndPowerfulOz

11-Jul-17 12:33

They don't all "enjoy" it. It's a job. Indeed, prostitutes go same-sex for their personal one-on-ones because opposite-sex has lost all appeal due to the "job".

#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun

Re: Does NULL <> 'string'?

Jagger B11-Jul-17 16:04

11-Jul-17 16:04

Sorry, You failed the interview. He'll call you next time. Hmmm | :|

Hmmm | :|

Re: Does NULL <> 'string'?

Jeremy Falcon11-Jul-17 16:13

11-Jul-17 16:13

So wait, you're saying I can't be a prostitute? Why? Because I'm a guy? That's discrimination.

Jeremy Falcon

Re: Does NULL <> 'string'?

Jagger B11-Jul-17 16:21

11-Jul-17 16:21

Are you saying, Sql Dev's = prostitute?

Re: Does NULL <> 'string'?

Jeremy Falcon11-Jul-17 16:36

11-Jul-17 16:36

Yes. Everyone knows you have go through prostitute training to get certified. You just finding this out? You may wanna talk to your MS rep.

Jeremy Falcon

Re: Does NULL <> 'string'?

Nelek12-Jul-17 2:27

12-Jul-17 2:27

Guys... this is not the soapbox

M.D.V.

Wink | ;)

If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.

Re: Does NULL <> 'string'?

TheGreatAndPowerfulOz17-Jul-17 11:04

TheGreatAndPowerfulOz

17-Jul-17 11:04

It's also not the lounge.

#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun

Re: Does NULL <> 'string'?

txmrm12-Jul-17 7:36

12-Jul-17 7:36

Like everything else in programming, it depends. In this case it depends on the SQL ansi_nulls setting. If this is a statement inside a SQL stored procedure, view, etc the ansi_nulls setting is burned into the object when it is created. If this is an ad-hoc statement then the ansi_nulls setting in effect for the SQL connection the statement is executed against is used.

If you want null <> 'value' to return true, you could just set ansi_nulls off. Not that I would recommend doing that...

Re: Does NULL <> 'string'?

Richard Deeming12-Jul-17 8:09

Richard Deeming

12-Jul-17 8:09

txmrm wrote:
Not that I would recommend doing that...

Especially as it's deprecated, and likely to be removed:

SET ANSI_NULLS (Transact-SQL) | Microsoft Docs[^]:
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Does NULL <> 'string'?

Kornfeld Eliyahu Peter12-Jul-17 8:43

Kornfeld Eliyahu Peter

12-Jul-17 8:43

It may be weird, but it's nothing new...
I learned SQL on mainframe, and the first thing we had to memorize about NULL is that it can not stand of either side of any of the comparison operators (=, <, >, <>)...

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: Does NULL <> 'string'?

obermd13-Jul-17 2:09

13-Jul-17 2:09

The SQL standards don't define how NULL should behave. What you found is accurate for one SQL dialect but may not be accurate for another. This is actually one of the biggest challenges when changing RDBMS vendors.

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.