• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • NULL and NOT IN

    November 16, 2011 Ted Holt

    No matter who you are, there’s always something you can learn. In Much Ado about Nothing: Interesting Facts about Null, I presented a good bit of information about null values in database tables. Imagine my surprise and delight to stumble upon something I did not know about nulls.

    It started innocently enough. I was surfing the Web and happened upon a link to an article entitled Ten Common SQL Programming Mistakes. I couldn’t click the link fast enough.

    I found a very well-written article by Plamen Ratchev. I won’t repeat the whole thing here, but Ratchev wrote about a problem of which I was not aware. This problem occurs when a list that follows NOT IN contains a null value.

    To illustrate, I’ll use Ratchev’s illustration, modified according to his suggestion.

    Assume two database tables.

    create table colors
     (color_code  char(3),
      color_name  char(10))
    
    insert into colors values
      ('BLK', 'Black'),
      ('BLU', 'Blue'),
      ('GRN', 'Green'),
      ('RED', 'Red')
    
    create table products
      (sku          dec (3,0),
       description  char(20),
       color        char(3))
    
    insert into products values
      (1, 'Ball', 'RED'),
      (2, 'Bike', 'BLU'),
      (3, 'Tent', null)
    

    Suppose you are asked for a list of the colors that are not used in any products. That would be black and green, right? But how do we derive that list using SQL? I would have done this:

    SELECT C.color_code
    FROM Colors AS C
    WHERE C.color_code NOT IN
            (SELECT P.color
               FROM Products AS P)
    

    And I would have been wrong, because the query would have returned an empty set. Here’s why.

    The system carries out the second SELECT first, then uses the resulting list of colors in the first SELECT, as if I had written this:

    SELECT C.color_code
    FROM Colors AS C
    WHERE C.color_code NOT IN
            ('RED', 'BLU', NULL)
    

    NOT IN is a shorthand way of saying the following:

    SELECT C.color_code
    FROM Colors AS C
    WHERE C.color_code <> 'RED'
      AND C.color_code <> 'BLU'
      AND C.color_code <> NULL)
    

    It is the last comparison that provides the problem. You cannot use the not-equal operator to test for NULL. The comparison is undefined. Therefore the WHERE fails every time.

    I was easily able to fix the query.

    SELECT C.color_code
    FROM Colors AS C
    WHERE C.color_code NOT IN
            (SELECT P.color
               FROM Products AS P
              WHERE P.color is not null)
    

    I’ve never worked in a shop that used null values in database tables, so I’ve never run into this problem. I expect many of you are in the same boat.

    My deepest and most sincere thanks to Plamen Ratchev for expanding my knowledge of SQL.

    RELATED STORY

    Much Ado about Nothing: Interesting Facts about Null



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    LaserVault

    Integrate Virtual Tape For Better Backups, Faster Recovery, And More Flexibility

    Virtual tape and virtual tape libraries offer a way to both simplify and strengthen backup and recovery operations. By incorporating virtual tape technology, automation of backups becomes possible resulting in hundreds of hours saved annually for IT departments and personnel.

    LaserVault ViTL is a virtual tape and tape library solution developed specifically for use with IBM Power Systems (from AS/400 to iSeries to Power 9s). See a demo and get a $50 gift card.

    With ViTL you can:

    • Replace physical tape and tape libraries and associated delays
    • Automate backup operations, including the ability to purge or archive backups
    • Remotely manage your backups – no need to be onsite with your server
    • Save backups to a dedupe appliance and the cloud
    • Recover your data at lightspeed greatly improving your ability to recover from cyberattacks
    • And so much more

    “The ViTL tapeless solution has truly made my job easier. It has given me more confidence in our full system recovery ability – but at the same time I hope it is never needed.” IBM i Administrator at a financial services company

    Sign-up now to see a ViTL online demo and get a $50 Amazon e-gift card when the demo is complete as our way of saying thanks for your time. Plus when you sign-up you’ll receive a free facts comparison sheet on using virtual tape vs tape so you can compare the functionality for yourself.

    LaserVault.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Micro Focus:  RUMBA for iSeries, the world's most used terminal emulation software
    The 400 School:  Fall Training Sale – Discounts up to 40%! RPG IV COBOL CL Admin Security
    ASNA:  Wings™ - The faster, easier way to a better System i user interface

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Linoma: DMZ Gateway Tightens File Transfer Security Feeling Like A Heel

    Leave a Reply Cancel reply

Volume 11, Number 35 -- November 16, 2011
THIS ISSUE SPONSORED BY:

Vision Solutions
SEQUEL Software
WorksRight Software

Table of Contents

  • NULL and NOT IN
  • Wow! I Could Have Had Long Column Names!
  • Putting Your i System in Semi-Restricted State

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20
  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.