SQL - NULL Values: Understanding the Unknown

Hello there, future database wizards! Today, we're going to dive into a fascinating aspect of SQL that often leaves newcomers scratching their heads - NULL values. Don't worry if you've never written a line of code before; I'll guide you through this concept step by step, just like I've done for countless students over my years of teaching. So, grab a cup of your favorite beverage, and let's embark on this SQL adventure together!

What is a NULL Value?

Before we jump into the nitty-gritty, let's understand what NULL actually means in SQL. Imagine you're filling out a form, and there's a field asking for your middle name. But what if you don't have a middle name? You can't write "no middle name" because that's not accurate - you're not saying you have a middle name called "no middle name". You simply leave it blank. That blank, my friends, is essentially what NULL represents in SQL.

In SQL, NULL is a special marker used to indicate that a data value does not exist in the database. It's not zero, it's not an empty string, it's the absence of any value. Think of it as SQL's way of saying, "I don't know" or "This information is not available."

Creating a Table without NULL Values

Now that we understand what NULL is, let's look at how we can create a table that doesn't allow NULL values. This is particularly useful when you have data that you absolutely must have for each record.

Example 1: Creating a Student Table

CREATE TABLE Students (
StudentID INT NOT NULL,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Age INT NOT NULL,
Email VARCHAR(100)
);

In this example, we're creating a table called "Students". Let's break down what's happening:

1. StudentID, FirstName, LastName, and Age are all declared as NOT NULL. This means these fields must have a value; they can't be left empty.
2. The Email field doesn't have NOT NULL, which means it's okay if a student doesn't provide an email address.

By using NOT NULL, we're telling our database, "Hey, we absolutely need this information for every student. No excuses!"

Updating NULL Values in a Table

Sometimes, you might find yourself with NULL values in your table that you want to update. Let's look at how we can do that.

Example 2: Updating NULL Email Addresses

Imagine we've realized that we need email addresses for all our students, and we want to update the ones that are currently NULL.

UPDATE Students
SET Email = '[email protected]'
WHERE Email IS NULL;

This query does the following:

1. It looks at the Students table.
2. For any record where Email IS NULL (remember, we can't use = NULL!), it sets the email to '[email protected]'.

This is like saying, "For any student who hasn't given us an email, let's use this placeholder email address for now."

Deleting Records with NULL Values

Sometimes, you might want to remove records that have NULL values in certain fields. Let's see how we can do that.

Example 3: Deleting Students without Age Information

DELETE FROM Students
WHERE Age IS NULL;

This query will remove any student records where the Age field is NULL. It's like saying, "If we don't know how old a student is, let's remove their record from our database."

Be careful with operations like this! Always make sure you really want to delete these records before running such a query.

Working with NULL in WHERE Clauses

One tricky thing about NULL values is that you can't use normal comparison operators with them. Let's look at how to properly check for NULL values in WHERE clauses.

Example 4: Finding Students with Missing Email Addresses

SELECT FirstName, LastName
FROM Students
WHERE Email IS NULL;

This query will give us a list of all students who haven't provided an email address. Notice we use IS NULL instead of = NULL. This is a common mistake that even experienced programmers sometimes make!

NULL and Aggregate Functions

NULL values can also affect how aggregate functions work. Let's look at an example.

Example 5: Calculating Average Age

SELECT AVG(Age) AS AverageAge
FROM Students;

This query calculates the average age of all students. Here's the interesting part: NULL values are completely ignored in this calculation. So if you have 10 students, but only 8 have their age filled in, the average will be calculated based on those 8 values.

Conclusion

And there you have it, folks! We've journeyed through the land of NULL values in SQL. We've seen how to create tables that don't allow NULLs, how to update and delete records with NULL values, and how to work with NULLs in our queries.

Remember, NULL isn't zero, it isn't an empty string, it's the absence of a value. It's SQL's way of shrugging its shoulders and saying, "I don't know." And now, you know how to handle these "I don't know" situations like a pro!

Keep practicing with these concepts, and before you know it, you'll be navigating the world of databases with confidence. Happy querying!

方法	描述
IS NULL	檢查一個值是否為 NULL
IS NOT NULL	檢查一個值是否不是 NULL
COALESCE()	回傳列表中第一個非 NULL 的值
IFNULL()	如果表達式為 NULL，則回傳指定的值
NULLIF()	如果兩個表達式相等，則回傳 NULL

Credits: Image by storyset