Help on retrieving null values in SQL

by Peter Haig
(The Rhondda, South Wales ,UK)

SELECT Distinct
persmain.IDENTIFIER ClientNo
,initcap(nvl(ltrim( persmain.FIRST_NAMES||' '||persmain.FAMILY_NAME) , 'Not Recorded')) NAME
,REPLACE(BOF_first_add(TO_NUMBER(( persmain.IDENTIFIER ))), 'County', NULL) ADDR
,initcap(nvl(ctitle.MEANING,'Not Recorded')) TITLE
,initcap(nvl(moretel.TELEPHONE_NUMBER,'Not Recorded')) MORETEL
,decode (oref.ertype_code,'HNO', REF.REFERENCE_ID, 'Null', 'Not Recorded' ) Hospital_No
PERSONS persmain,
( persmain.IDENTIFIER = '178998')
and (moretel.PERSON_IDENTIFIER(+) = persmain.IDENTIFIER)
and (Moretel.Primary_Contact_Indicator = 'Y')
and oref.person_identifier(+) = persmain.identifier

The above code returns data for all the select items and shows the expected value 'null' for Hospital_No. (Client has no other references recorded).

However if the client has other references recorded the result for Hospital_No shows all the other references expected but I want to show only the result that refers to a specific type of reference code in this case the code would be 'HNO'. I can achieve this by adding the line "And oref.ertype_code = 'HNO' as the last line of the SQL.

By adding this line the query returns nothing at all for clients with no 'other references' recorded and the Hospital_No for those with this reference number

I want to retrieve all the select statements for those who have a Hospital_No recorded and the same for those clients without a Hospital_No but showing 'Not Recorded' instead of the hospital number.

Comments for Help on retrieving null values in SQL

Average Rating starstarstarstarstar

Click here to add your own comments

Nov 04, 2016
NULL values in Oracle SQL
by: John

Not knowing the table structures we can only comment in general terms. Dissecting the query however we can see that you're doing SQL outer joins from the PERSONS table to CODED_TITLES, TELEPHONES and OTHER_REFERENCES tables. We assume from the way you've written your SQL query that the PERSONS table will always have values for the foreign keys to the other tables. If not you would need to reverse the outer joins.

We can demonstrate this quite easily. Let's create a couple of tables emp and dept as follows:

CREATE TABLE emp (emp_id NUMBER, emp_name VARCHAR2(200), dept_id NUMBER);

CREATE TABLE dept (dept_id NUMBER,
                   dept_name VARCHAR2(30));

and populate them like so:-

insert into emp values(1,'Mike Smith',1);
insert into emp values(2,'Bill Jones',2);
insert into dept values (1,'IT');
insert into dept values (3,'HR');

Notice that there's no department 2 in the dept table so if we want any data to be returned from a query joining the 2 tables we need an outer join. If it's emp that's missing the record then the SQL would look like this:

>select emp_id,emp_name,dept_name from emp,dept
where emp.dept_id(+)=dept.dept_id

which would produce the following results:

emp_id  emp_name       dept_name
------  -----------    ----------
1       Mike Smith     IT

Notice that there's no values for emp_id or emp_name for the 2nd record. This is because there are no rows in the emp table with dept_id equal to 3.

If we reverse the outer join in our SQL query like so:

select emp_id, emp_name,dept_name from emp,dept
where emp.dept_id=dept.dept_id(+);

we get the following results:

emp_id  emp_name       dept_name
------  -----------    ----------
1      Mike Smith     IT
2      Bill Jones   

This time we get all the records from emp and if there's a match on dept_id we get dept_name as well.

To get back to the question, though, we need to know how to deal with missing values. In Oracle these are known as NULL which means they are not known/undefined/don't exist. The mistake made though is in treating NULL as if it has a value of 'Null' in the DECODE of ertype_code.
decode (oref.ertype_code,'HNO', OREF.REFERENCE_ID, 'Null', 'Not Recorded' ) Hospital_No
This is a common mistake and very easy to do. NULL is a reserved word in Oracle and the query optimizer recognizes it so don't put in quotes as doing so converts it to a character string which is then by definition NOT NULL so will never match NULL which is an unknown/undefined value.

Let's demonstrate. The following query changes the value of dept_name from 'IT' to 'geeks' and if dept_name is not defined (i.e. it is NULL) to 'Unknown'.

select emp_id, emp_name, decode(dept_name, 'IT', 'geeks', null, 'Unknown') from emp,dept
where emp.dept_id=dept.dept_id(+)

and produces these results:

emp_id  emp_name       dept_name
------  -----------    ----------
1       Mike Smith     geeks
2       Bill Jones    Unknown

In summary, therefore, the SQL query needs to use the reserved word NULL rather than the string 'Null' in the decode clause and may need to reverse the outer join.

See also this question and answer on the use of NULLs What is the answer of 100 added to null in SQL?

You might also be interested in our Oracle SQL tutorials or see our Oracle training page if you're interested in some formal training either on-line or in person.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.