Working with NULL values

Introduction to Oracle SQL

Hadrien Lacroix

Content Developer

What are NULL values?

No value
Not the same as 0
Arithmetic expressions with NULL evaluate to NULL
- NULL + 10 = NULL
Aggregate functions usually ignore NULL values
- COUNT doesn't count NULL values in a columns

Why do we care about null values?

Real world data isn't perfect.

Clean data
Analyze missing data

Testing if a value is NULL

= can't be used to test for NULL values

Instead use:

IS NULL

SELECT * FROM Customer WHERE LastName IS NULL

IS NOT NULL

SELECT * FROM Customer WHERE LastName IS NOT NULL

NVL

NVL(x, y): convert x, which may contain a null value, to y, a non-null value.

SELECT NVL(HireDate, '11/19/2004')
FROM Employee

NULLIF

NULLIF(x, y): Compares x and y, returns

NULL if x=y
x if they are not equal

SELECT c.CustomerId, i.BillingCity, c.City, NULLIF(i.BillingCity, c.City) 
FROM Invoice i, Customer c

| CustomerId | BillingCity | City      | NULLIF |
|------------|-------------|-----------|--------|
| 48         | Oslo        | Amsterdam | Oslo   |
| 49         | Boston      | Vienne    | Boston |
| 59         | London      | London    | NULL   |

COALESCE

COALESCE: returns first non-null value in a list

SELECT CustomerId, COALESCE(phone, email, fax) AS ContactMethod
FROM Customer

| CustomerId | ContactMethod           |
|------------|-------------------------|
| 59         | +91 080 22289999        |
| 58         | [email protected] |
| 57         | +56 (0)2 635 4444       |

Let's practice!

Introduction to Oracle SQL