Gullible about Nullable – Stuff I thought I knew…and didn’t

Taking a break from the advanced mathematical theory required to work out all the permutations that will allow England to qualify from their group, I thought that, by way of light relief, I’d have a look at NULL in SQL.

I was recently disabused of a long-held notion that NULL will always evaluate to TRUE. I can’t remember for the life of me where I acquired this delusion. It’s never been particularly relevant to me as, whenever I’m likely to be faced with such a situation, I simply use the NVL function.

I’m wondering – is it just me – is that something I’ve mis-remembered from my days as an Oracle neophyte back when the world was young?

For demonstration purposes, I will be enlisting the help of Simon once again, as well as a certain Claude Gnakpa, the gallic import currently plying his trade in the salubrious setting of Kenilworth Road.
This somewhat esoteric choice can be explained quite simply…Simon bet me that I couldn’t work a reference the said Monsieur Gnakpa into a blog post.

Look, he’s a Luton Town fan and so has few pleasures in life. I just thought I should humour him.

In terms of relational theory, NULL represents an unknown value. As I now know, it will always evaluate to TRUE…or will it ?

Let’s have a look. As we’re talking about truth, we’re also talking – philosophically speaking – about beauty, hence the table names.

CREATE TABLE beauty( 
    bloke_name VARCHAR2(30)) 
/ 
CREATE TABLE truth( 
    bloke_name VARCHAR2(30)) 
/ 

INSERT INTO beauty VALUES( 'MIKE'); 
INSERT INTO beauty VALUES( 'SIMON'); 
INSERT INTO beauty VALUES( 'CLAUDE'); 

INSERT INTO truth VALUES( 'MIKE'); 
INSERT INTO truth VALUES( 'SIMON');

All seems straightforward enough ….

/* Equi-Join */
SQL> select b.bloke_name 
  2  from beauty b, truth t 
  3  where b.bloke_name = t.bloke_name; 

BLOKE_NAME 
-------------------------------------------------------------------------------- 
MIKE 
SIMON 
 
SQL> 

/* Set Query */
SQL> select bloke_name 
  2  from beauty 
  3  minus 
  4  select bloke_name 
  5  from truth; 

BLOKE_NAME 
-------------------------------------------------------------------------------- 
CLAUDE 

SQL> 

/* Correlated sub-query */
SQL> select b.bloke_name 
  2  from beauty b 
  3  where b.bloke_name NOT IN ( 
  4  	SELECT t.bloke_name 
  5     FROM truth t 
  6  	WHERE t.bloke_name = b.bloke_name); 

BLOKE_NAME 
-------------------------------------------------------------------------------- 
CLAUDE 

SQL> 

/* Non-correlated sub-query */
SQL> SELECT b.bloke_name 
  2  FROM beauty b 
  3  WHERE b.bloke_name NOT IN ( 
  4  	SELECT t.bloke_name 
  5  	FROM truth t); 

BLOKE_NAME 
-------------------------------------------------------------------------------- 
CLAUDE 

SQL> 

All quite straightforward….and nothing to do with NULLs. Now, let’s do something innocuous like :

INSERT INTO truth VALUES( NULL);

Hardly seems any point in running those queries again. Well, just humour me …

/* Equi-Join */
SQL> SELECT b.bloke_name 
  2  FROM beauty b, truth t 
  3  WHERE b.bloke_name = t.bloke_name; 

BLOKE_NAME 
-------------------------------------------------------------------------------- 
MIKE 
SIMON 

SQL> 
/* Set Query */
SQL> SELECT bloke_name 
  2  FROM beauty 
  3  MINUS 
  4  SELECT bloke_name 
  5  FROM truth; 

BLOKE_NAME 
-------------------------------------------------------------------------------- 
CLAUDE 

SQL> 
/* Correlated Sub-Query */
SQL> SELECT b.bloke_name 
  2  FROM beauty b 
  3  WHERE b.bloke_name NOT IN ( 
  4  	SELECT t.bloke_name 
  5  	FROM truth t 
  6  	WHERE t.bloke_name = b.bloke_name); 

BLOKE_NAME 
-------------------------------------------------------------------------------- 
CLAUDE 

SQL> 

/* Non-correlated Sub-query */
SQL> SELECT b.bloke_name 
  2  FROM beauty b 
  3  WHERE b.bloke_name NOT IN ( 
  4  	SELECT t.bloke_name 
  5     FROM truth t); 

no rows selected 

SQL> 

Hang on, run that by me again. The non-correlated sub-query now returns no rows, zero, none, nix ( and indeed, null).
Let’s look at that query again :

/* Give me all the rows in beauty...*/
SELECT b.bloke_name
FROM beauty b 
WHERE b.bloke_name NOT IN ( /* … that don't exist in truth */
	SELECT t.bloke_name
	FROM truth t);

On this occasion the result would seem to suggest that there are no rows in beauty that are not also in truth. Effectively therefore, ‘CLAUDE’ = NULL.

At this point, I could wander off to explore the intracacies of relational theory, from whence the whole concept of NULL derives. I could start to delve into DeMorgan’s Law and subject my brain to yet more complicated maths. However, the highlights show has just started and the vuvuzelas are calling. Therefore, striking a blow for philistines everywhere…

SQL> SELECT b.bloke_name
  2  FROM beauty b
  3  WHERE b.bloke_name NOT IN (
  4  	SELECT NVL( t.bloke_name, 'SALLY')
  5  	FROM truth t);

BLOKE_NAME
--------------------------------------------------------------------------------
CLAUDE

SQL> 

Now, if the USA win 4-3 and England scrape another 0-0…

About these ads

One thought on “Gullible about Nullable – Stuff I thought I knew…and didn’t

  1. In that far off theme park called Teradataland (the rides are big, but a bit dull), we’re always taught that NULL effectively has no meaning – it cannot be equated to (or, by derivation, specifically not equated to), so NULL = NULL is false, NULL NULL is also false as is = NULL etc. etc. – it can only be tested using IS NULL and IS NOT NULL. There are a few functions like ZeroIfNULL and COALESCE that deal with it as well, similarly to NVL.

    All the above examples have identical outcomes on Teradata, incidentally, except the NVL one, as that doesn’t exist as a keyword on T12.

    However, the most efficient method I’ve found to do what you’re last query is doing (on Teradata) is:
    SELECT b.bloke_name
    FROM
    beauty b
    LEFT JOIN
    truth t
    ON t.bloke_name = b.bloke_name
    WHERE t.bloke_name IS NULL
    ;

    Don’t let the DBAs tell you otherwise – this works when other methods don’t (on huge tables when properly indexed) and is significantly faster.

    I tried to work in ‘RIGHT JOIN Claude’, but it got too contrived – sorry, that’s an in-joke that shouldn’t go on blogs, I guess.

    Gotta do some real work now – day off tomorrow vuvuzela-ing for Engerland.

    Cheers

    Si M…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s