SQL Course - Part 5. On Nothing

If you need a reminder on how to set up the environment for this course, see the Environment and Basic Select posts.

Sometimes you don't know what to enter as a column value; for example, you don't know the publication year or the number of pages of a certain book. SQL has a special value to handle just that: NULL. NULL means "no data".

You can insert NULL into a column of any type (number, text, date, or time). However, the database architect can decide that for some columns NULLs are not allowed. In this case, you always have to give some NOT NULL value to this column.

Insert data

First, insert some NULL data into our database. Copy the queries into the query field in HSQLDB client and click Execute SQL. This inserts three new books with NULL publication year or NULL number of pages into our database.

insert into book values (1000, 'Head First Java', NULL, NULL, 'prog'); 
insert into book values (1001, 'Mastering Regular Expressions', 2005, NULL, 'prog'); 
insert into book values (1002, 'The Art and Science of Java', NULL, 75, 'prog'); 

Now select the data.

SELECT * FROM book WHERE book_id >= 1000; 

NULL values are displayed as empty fields

As you can see, NULL values are displayed as empty fields.

NULLs in conditions

Let's see how NULL values behave in WHERE clauses. Run the following query.

SELECT * FROM book WHERE book_id >= 1000 AND publication_year != 2005; 

You should see the following result:

Nothing is returned

Perhaps you expected that the query would return rows with id 1000 and 1002. However, the result is no rows. Why? It's because of the way SQL handles NULL values. The query returns a row if the value of the WHERE clause is true. However, SQL can tell that a number is different from 2005 only if it knows its value. If it doesn't know its value, the result is not true, it's unknown.

SQL uses a three-valued logic with logical values true, false, and unknown. This particular three-valued logic (yes, there are more three-valued logics!) is called Łukasiewicz logic (the pronounciation of Łukasiewicz is something like "Woockashevitch"). This logic is sometimes referred to as Heyting-Kleene-Łukasiewicz logic. You can find more info on Łukasiewicz logic and its use in SQL in the Wikipedia.

IS NULL / IS NOT NULL

If you want a query to return rows with NULL values, you have to explicitly ask for those rows. There are special operators: IS NULL which tests if a given value is NULL and IS NOT NULL which tests if a given value is not NULL.

Let's see an example:

SELECT * FROM book WHERE book_id >= 1000 AND 
   (publication_year != 2005 OR publication_year IS NULL); 

Now the query returns what you expect:

Two rows returned

OneWebSQL

OneWebSQL has static methods: LExp.isNull(AExp) and LExp.isNotNull(AExp) to invoke IS NULL and IS NOT NULL operators. An example which runs the previous SQL query:

List<Book> books = bookDAO.getBookList( 
    LExp.and( 
        BookDAO.BOOK_ID.ge(1000), 
        BookDAO.PUBLICATION_YEAR.ne(2005).or( 
            LExp.isNull(BookDAO.PUBLICATION_YEAR)))); 

Have you ever had trouble with NULLs?