Oracle conditions and how they handle NULL

Two students in my database class came to me yesterday with questions about a nested query they had written. The query looked fine, but when executed the query returned no rows. After considerable investigation I realized the subquery being evaluated against was not only returning the obvious values, but also returned NULL.

It turns out NULL gets very special treatment in Oracle. NULL is treated as unknown. Basically it cannot be evaluated against anything because you can’t evaluate something you can’t measure. To try to make this clear I offer the following examples:

Note: dual is a table used frequently in testing. It has no data but can be used to return calculations, text, test conditions, etc.

Here’s a basic select and condition that will always succeed:

SELECT 'true' FROM dual WHERE 1 = 1;

'TRU
----
true

I think we can agree that, at least in the reality where we care to evaluate database queries, that 1=1, so this query returns the rows selected. In this case we have only selected one row, the string ‘true’

Now let’s take a look at the unusual behavior of NULL. First, here’s a query that should return no rows:

SELECT 'true' FROM dual WHERE 1 = NULL;

no rows selected

This makes sense because NULL does not equal 1, but now let’s look at another form of this statement:

SELECT 'true' FROM dual WHERE 1 != NULL;

no rows selected

Logically we think that 1 is different from NULL, so this should have returned ‘true’, but Oracle has a different idea. Oracle evaluates this by asking “Does 1 not equal an unknown value?” This makes as much sense to Oracle as asking “Does 3.17 equal a tree?” or “Is my birthday red?” so no matter what makes sense to us, Oracle evaluates this condition as FALSE.

We can take this one step further by executing the following query:

SELECT 'true' FROM dual WHERE NULL = NULL;

no rows selected

This illustrates that Oracle is completely unwilling to even try to evaluate NULL, but it starts to make sense that you would not say one unknown is, or isn’t equal to another unknown; therefore, NULL cannot be said to either be equal, or not equal to NULL.

Now let’s take a look at an IN condition.

SELECT 'true' FROM dual WHERE 5 NOT IN (1, 2, 3);

'TRU
----
true

This IN statement returns TRUE because 5 is NOT IN the set of 1, 2, 3. Now let’s look at a slight variation.

SELECT 'true' FROM dual WHERE 5 NOT IN (1, 2, 3, NULL);

now rows selected

While 5 does not explicitly appear in the set, we do not know what NULL is. Since we cannot evaluate on the unknown NULL, the condition fails and no rows are returned.

So we can see that NULL must be handled as a special case. To handle this we must use IS NULL or IS NOT NULL. If we want to evaluate two values to see if they are both NULL we could use the following:

SELECT 'true' FROM dual WHERE NULL IS NULL;

'TRU
----
true

Here we see that, while NULL = NULL is not a valid condition, NULL IS NULL works just fine. Now let’s consider this in the context of a subquery.

SELECT first_name, last_name FROM faculty
WHERE id NOT IN (SELECT instructor_id FROM class);

This query will be valid only if the subquery SELECT instructor_id FROM class does not return any NULL values. If there are entries in the class table which have NULL values in the instructor_id column, the WHERE condition will always fail and no rows will be returned.

To make this statement more reliable (since we may plan not to have any NULL values now but some may make it in there) we can add a condition to the subquery.

SELECT first_name, last_name FROM faculty
WHERE id NOT IN (
SELECT instructor_id FROM class
WHERE instructor_id IS NOT NULL);

Now the result set from the subquery will never contain NULL and the condition will be properly evaluated.

oracle, dba, database administration, database, database programming

Oracle on Solaris: 32-bit or 64-bit

It is important for optimal performance to make sure you match up your Oracle RDBMS installation with your OS. Running a 32-bit version of Oracle on a 64-bit OS is may not give you peak performance, but also will not be able to address large segments of RAM and large files. So how do you know what your OS supports? How can you tell if that Oracle install from before you started is 64-bit? Here’s how:

Is my Operating System 64-bit?

In Solaris, from the command line (you don’t have to be root in most cases) run this command:

/usr/bin/isainfo -kv

If your OS is 64-bit, you will see output like:

64-bit sparcv9 kernel modules

If your OS is 32-bit, you will get this output:

32-bit sparc kernel modules

For Linux users

If you are running Linux, you can check your distribution with the uname command:

uname -m

The output will read x86_64 for 64-bit and i686 or similar for 32-bit.

How about this Oracle install? Is it 64-bit?

The question here is weather your Oracle binaries are 64-bit. While some of the binaries associated with Oracle may be 32-bit, the important ones will be 64 bit. To check those, follow these steps from the command line:

cd $ORACLE_HOME/bin
file oracl*

This will display the file type of your oracle binaries. If you are running 64-bit binaries, the output should look like this:

oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped
oracleO: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

If your binaries are 32-bit, the output will look like this:

oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

If you find you are running 32-bit and decide to go to 64 be careful. The switch can be a bit tricky. Read the documentation closely and make sure your service contract is payed up!

oracle, dba, database administration, database, solaris, linux, sun, sun microsystems, 32-bit, 64-bit

Converting Time Zones in Oracle

In my previous article Oracle, SQL, Dates and Timestamps I talked about inserting, selecting and comparing dates in Oracle. Now I want to cover some functionality for converting between time zones.

We’ll use the table in the form specified in the previous article, but insert one more row:

insert into dates values(6, to_date('09/20/05 23:15', 'MM/DD/YY HH24:MI'));

The contents of the table now look like this:

1 09/14/05, 21:08
2 09/27/05, 00:00
3 10/02/05, 22:05
4 09/01/05, 17:01
5 09/12/05, 14:30
6 09/20/05, 23:15

Changing Time Zones

The date format in Oracle does not contain time zone information, but the database does. To find out the time zone set, execute this query:

SELECT dbtimezone FROM dual;

DBTIME
------
-04:00

The time zone can be updated with the command:

ALTER database SET TIME_ZONE = '-05:00';

where you can specify the offset from Greenwich mean time or a valid time zone from the list in the v$timezone_names view. Note that this is one of the few of the ‘v$’ views which are plural.

Switching Time Zones

The function new_time is used to convert a time to different time zones. To illustrate this we’ll look at entry 5 from the dates file.

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 02:30 PM

This database is in US Eastern time but we want to display the time in US Central.

SELECT entry, to_char(new_time(entry_date, 'EST', 'CST'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 01:30 PM

Here we clearly see the time converted to Central. Note that the new_time function is performed on the date field, not on the to_char. Now let’s grab this time in Pacific time:

SELECT entry, to_char(new_time(entry_date, 'EST', 'PST'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=5;

5 09/12/05 11:30 AM

Now we see not only the time converted, but also the time of day has gone from PM to AM.

Now let’s take a look at entry 6:

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=6;

6 09/20/05 11:15 PM

We’ll again assume this timestamp is in US Eastern time, but let’s convert it this time to Greenwich Mean Time.

SELECT entry, to_char(new_time(entry_date, 'EST', 'GMT'), 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=6;

6 09/21/05 04:15 AM

This shows not only the change in hours, but that the date of this entry is displayed properly for its time zone.

Of course the new_time function can be used on inserts in the same way. This is useful if you are allowing input from people in different geographical regions. Here we convert an entry made in Pacific Time to Eastern:

INSERT INTO dates
VALUES (7,
new_time(to_date('09/22/05 10:28 AM', 'MM/DD/YY HH:MI AM'), 'PST', 'EST'));

SELECT entry, to_char(entry_date, 'MM/DD/YY HH:MI AM') FROM dates WHERE entry=7;

7 09/22/05 01:28 PM

So we have converted 10:28 AM Pacific to 1:28 PM Eastern so all our entries in the table are consistent. Of course when performing the insert we need to put the to_date function within the new_time function so the text string is converted to a date format before we try to convert it.

oracle, sql, dba, database administration, database development

SQL Join Syntax

I have always found the different types of joins a bit confusing, but now thanks to a little experimenting I think I have a handle on it. This is geared toward Oracle, but most of this is ANSI SQL, so should work in other databases as well. Some of these features may not be available in Oracle pre-9i.

Assume these two tables:

SELECT * FROM faculty;

ID FIRST_NAME LAST_NAME
1 Jon Emmons
2 Zach Tirrell
3 Evelyn Stiller

SELECT * FROM class;

CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 CS3600 1
2 CS3020 2
3 CS2000  
4 CS1100 1

Inner Join

The most common (and simple) join. This will select the rows which satisfy the join condition; however if a row exists in one table but does not have a counterpart to fulfill the join condition those rows (from either table) will be ignored.

SELECT *
FROM faculty, class
WHERE faculty.id = class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
2 Zach Tirrell 2 CS3020 2
1 Jon Emmons 4 CS1100 1

Here we see the three rows where faculty.id had a match to class.instructor_id.

Left Join

A left join, a.k.a. left outer join will return all the row combinations which meet the join condition plus any rows from the first table which do not meet the guard condition.

SELECT *
FROM faculty LEFT JOIN class
ON faculty.id=class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
1 Jon Emmons 4 CS1100 1
2 Zach Tirrell 2 CS3020 2
3 Evelyn Stiller      

Now we see all the results we saw in the inner join, but we additionally see the row from faculty (id 3, Evelyn Stiller) which has no corresponding row in class.

Right Join

Also referred to as right outer join, this will show all row combinations which meet the join criteria, but will additionally show any rows from the second table which do not have counterparts in the first.

SELECT *
FROM faculty RIGHT JOIN class
ON faculty.id=class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
2 Zach Tirrell 2 CS3020 2
      3 CS2000  
1 Jon Emmons 4 CS1100 1

Now we see rows from the class table which do not have a faculty counterpart.

Outer Join

The outer join, or full outer join can be thought of a left join and right join. Rows which meet the join condition will of course be displayed, additionally, rows from both the first and second table referenced will be displayed.

SELECT *
FROM faculty FULL OUTER JOIN class
ON faculty.id = class.instructor_id;

ID FIRST_NAME LAST_NAME CLASS_ID CATALOG_NUMBER INSTRUCTOR_ID
1 Jon Emmons 1 CS3600 1
1 Jon Emmons 4 CS1100 1
2 Zach Tirrell 2 CS3020 2
3 Evelyn Stiller      
      3 CS2000  

Now we see all the rows from both the tables, joined where the join condition is met, or with corresponding null values where the join condition failed.

oracle, sql, dba, database administration, database development

Oracle, SQL, Dates and Timestamps

A common question amongst database developers is “How do I get dates and time into and out of the database in the format my script/program/table expects?” This information is based on Oracle, however I expect much of this will apply to other databases.

About the DATE and TIMESTAMP datatypes: The DATE datatype is 7-bytes, comprised of date and time information to the precision of 1 second.
TIMESTAMP can be from 7 to 11 bytes depending on the precision specified. Timestamps can represent date and time as small as the nanosecond (.000000001 seconds) The default is to a microsecond of precision (.000001 seconds.)

Note: dual is a special table for testing and development. It’s useful for returning values (results from functions or contents of variables) not stored in tables, for instance the current date.

Let’s start with some information we can grab from the system about the current date and time.

SELECT sysdate FROM dual;

SYSDATE
---------
14-SEP-05


This simple select statement returns the date in the standard format (typically DD-MON-YY.)

SELECT systimestamp FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
14-SEP-05 04.06.31.264201 PM -04:00

Here we see that systimestamp reports more detail than sysdate, including the offset from GMT.

Displaying Dates

The to_char function will allow you to describe how you want dates displayed and will convert them to a character string in that format. The default in Oracle is DD-MON-YY. The default format can be changed by setting the nls_date_format parameter.

SELECT to_char(sysdate, 'MM/DD/YYYY') FROM dual;

TO_CHAR(SY
----------
09/14/2005

As seen here, the to_char function requires two parameters: a date to display, and the format you want it to be in. There are dozens of formatting options, but here are some common ones:

SELECT to_char(sysdate, 'MM/DD/YY') FROM dual;

09/14/05

SELECT to_char(sysdate, 'MM/DD/YYYY HH:MI:SS') FROM dual;

09/14/2005 04:09:03

SELECT to_char(sysdate, 'DAY, MONTH DD, HH12:MI AM') FROM dual;

WEDNESDAY, SEPTEMBER 14, 04:09 PM

SELECT to_char(sysdate, 'YYYY BC') FROM dual;

2005 AD

SELECT to_char(systimestamp, 'HH24:MI:SS.FF3') FROM dual;

16:09:24.606

There are several more options than are displayed here. Your databases documentation should have a full list. These components can be used in any order or combination. These characters, as well as spaces can be used to format dates / . – : . ;

Inserting Dates

to_date works similarly to the to_char function above. You must specify a date, typically enclosed by single quotes, then describe the format with the date components as above. To demonstrate this we’ll create a table we can insert some dates into.

CREATE TABLE dates
(
entry NUMBER,
entry_date DATE,
CONSTRAINT pk_dates PRIMARY KEY (entry)
);

Now a few inserts:

INSERT INTO dates (entry, entry_date)
VALUES (1, sysdate);

Inserts the current date and time to the second.

INSERT INTO dates (entry, entry_date)
VALUES (2, to_date('09/27/05', 'MM/DD/YY'));

INSERT INTO dates (entry, entry_date)
VALUES (3, to_date('10/02/2005 10:05:33 PM', 'MM/DD/YYYY HH:MI:SS AM'));

INSERT INTO dates (entry, entry_date)
VALUES (4, to_date('17:01:24', 'HH24:MI:SS'));

INSERT INTO dates (entry, entry_date)
VALUES (5, to_date('Monday, September 12, 2:30 PM', 'DAY, MONTH DD, HH:MI AM'));

COMMIT;

Now let’s take a look at the data in the dates table:

SELECT entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM')
FROM dates;

1 09/14/2005 09:08:32 PM
2 09/27/2005 12:00:00 AM
3 10/02/2005 10:05:33 PM
4 09/01/2005 05:01:24 PM
5 09/12/2005 02:30:00 PM

We can see that the current date and time was entered in entry 1 down to the second.

Entry 2 contains the date we entered, but since we did not specify the time it has defaulted to midnight.

Entry 3 shows a complete timestamp exactly as we specified.

In entry 4 we see the time as we specified, but since we didn’t specify a date it has defaulted to the first of this month. I have a feeling this varies from database to database. Probably best not to rely on this.

Entry 5 shows the date and time, however since we did not specify seconds they display as :00.

Comparing Dates

Dates can be compared much like other values. To demonstrate this we’ll do some quick selects on the table we just created.

select entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM')
from dates
where entry_date > to_date('09/20/2005', 'MM/DD/YYYY');

2 09/27/2005 12:00:00 AM
3 10/02/2005 10:05:33 PM

select entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM')
from dates
where entry_date < sysdate; 1 09/14/2005 09:08:32 PM 4 09/01/2005 05:01:24 PM 5 09/12/2005 02:30:00 PM select entry, to_char(entry_date, 'MM/DD/YYYY HH:MI:SS AM') from dates order by entry_date; 4 09/01/2005 05:01:24 PM 5 09/12/2005 02:30:00 PM 1 09/14/2005 09:08:32 PM 2 09/27/2005 12:00:00 AM 3 10/02/2005 10:05:33 PM

Those are the highlights. Most things you'll need to do will be some type of variation on these.

oracle, sql, dba, database administration, database development