Conference speakers: Why do you teach at events?

I’ve put up a Twitter poll to find some answers to the question: Why do speakers teach at events? Especially at events where there’s no compensation.

I know more or less why I do this, but I’ll hold off on my answer. I want to hear about you.

An MCM teaches COMMENTS at SQL Saturday OKC

Comments

Edit: SQL Sat OKC has come and gone, but the new session on comments is recorded and up on the MidnightDBA Events page! (Direct link to WMV.)

Tomorrow (as of the blog’s publish date) at8:30am, I will be teaching “T-SQL’s Hidden Support Feature” at SQL Saturday Oklahoma City (totally free, did you know? Free IT training, y’all.)

I said in a recent blog about comments that this session started in my head years ago, and launched a few weeks ago when I was double-dog-dared to write it by Oklahoma City user group members.

I wrote the session in about an hour. It was that fully formed already.

WHY ON EARTH am I talking about comments, of all things? How too, too common, dahling.  Simply this: Comments give you the biggest possible ROI for code support.  Come by tomorrow (or see the recording afterward), and I’ll tell you why and how. 

See me, and maybe a couple of dozen other speakers, teaching all the things SQL Saturday OKC tomorrow.

-Jen

 

Tricky TSQL: NOT IN (NULL)

We’ve done this before, but we can go one better this time.

Let’s take this step by step.

NULL means “I don’t know”. It stand for an unknown value.

Nothing can be equal to NULL. We simply can’t say that 1 = NULL, or ‘ABBA’ = NULL,  because we don’t know what value NULL might possibly be in the real world.

Nothing can be NOT equal to NULL. We can’t say that 1 <> NULL, or ‘ABBA’ <> NULL. NULL may well turn out to be 1, or ABBA, or whatever value we’re trying to check. We simply don’t know.

A simple example

So let’s take a very simple example, to demonstrate how this works. This particular query will return the value ‘True’, because 1 is demonstrably equal to 1:

IF 1 = 1
   SELECT   'True!';

Yep, that makes sense. I can tell: 1 = 1.

This query will not return ‘True’; it will return nothing, because 1 cannot be proven to be equal to NULL:

IF 1 = NULL
   SELECT   'True!';

We have an unknown, and nothing can possibly be equal to the unknown.

Another way to put this, is to use the keyword IN:

IF 1 IN ( SELECT    NULL AS a )
   SELECT   'True';

That parenthetical SELECT NULL simply returns NULL, so the query ends up asking: “is the value 1 in the set of values (NULL)?” We have no idea, so the query does not return ‘True’.

Almost the same thing, but trickier

Here’s yet another query that will not return ‘True’; 1 cannot be proven to be NOT equal to NULL:

IF 1 <> NULL
   SELECT   'True!';

NULL is unknown, and nothing can possibly be NOT equal to the unknown.

NULL will mess up your  NOT IN queries

And the absolute trickiest bit – the one that will trip you up, if you’re not thinking? NOT IN.

This query also returns nothing!

IF 1 NOT IN ( SELECT    2 AS a
              UNION
              SELECT    NULL AS a )
   SELECT   'True';

The IF statement asks: “is 1 in the collection of values (2, NULL)?” And we must say, “ah, no idea. That can’t be proven. Therefore we can’t return true.”

The value 1 cannot be determined to not be in the set of (2, NULL).

Play around with these, let it sink in. And watch out for NULLs in your NOT IN result sets!

One Fix

So, always use IS NULL or IS NOT NULL for your NULL comparison needs. And if you have a [value] NOT IN situation, read on.

Here’s what I did* to fix this exact situation in a recent query:

 SELECT ID
 FROM   Table1
 WHERE  ID NOT IN ( SELECT  ID
                    FROM    Table2
                    WHERE   ID > 0 );

While we could have used ID IS NOT NULL instead of ID > 0, it would make any relevant index on Table2 useless to us. In this particular case, ID is restricted to only positive above 0, and NULL.

To read up on even more solutions, see Mladen Pradjic’s article JOIN vs IN EXISTS.

Happy days,
Jen
www.MidnightDBA.com/Jen

 

 

Real news, real tech.