You are currently viewing SQL 1.11 SQL NULL Values

SQL 1.11 SQL NULL Values

Understanding SQL NULL Values


SQL NULL values represent missing or unknown data in a database. It’s important to understand how NULL values work, as they can affect query results and data integrity. In this tutorial, we’ll cover the basics of SQL NULL values, including how to insert, query, and handle NULL values in SQL.

Creating a Table with NULL Values


To demonstrate NULL values, let’s create a simple table named students with columns for student_id, name, and age. We’ll allow the age column to contain NULL values.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

Inserting Data with NULL Values


Now, let’s insert some data into the students table. We’ll include both NULL and non-NULL values for the age column.

INSERT INTO students (student_id, name, age) VALUES
(1, 'John Doe', 25),
(2, 'Jane Smith', NULL),
(3, 'Alice Johnson', 30);

Here, we inserted three records. John Doe has an age of 25, Jane Smith’s age is unknown (NULL), and Alice Johnson is 30 years old.

Querying NULL Values


Let’s query the students table to see how NULL values are handled in SQL queries.

a. Selecting all columns:

SELECT * FROM students;

Output:

| student_id |   name        | age |
|------------|---------------|-----|
|     1      | John Doe      |  25 |
|     2      | Jane Smith    | NULL|
|     3      | Alice Johnson |  30 |

b. Filtering rows with NULL values:

SELECT * FROM students WHERE age IS NULL;

Output:

| student_id |   name      | age |
|------------|-------------|-----|
|     2      | Jane Smith  | NULL|

c. Filtering rows with non-NULL values:

SELECT * FROM students WHERE age IS NOT NULL;

Output:

| student_id |   name        | age |
|------------|---------------|-----|
|     1      | John Doe      |  25 |
|     3      | Alice Johnson |  30 |
  1. Handling NULL Values:
    When working with NULL values, it’s essential to handle them properly in queries to avoid unexpected results.

a. Using COALESCE function to replace NULL values:

SELECT student_id, name, COALESCE(age, 'Unknown') AS age FROM students;

Output:

| student_id |   name        |   age   |
|------------|---------------|---------|
|     1      | John Doe      |   25    |
|     2      | Jane Smith    | Unknown |
|     3      | Alice Johnson |   30    |

Here, the COALESCE function replaces NULL values with ‘Unknown’.

b. Handling NULL values in calculations:

SELECT AVG(age) AS avg_age FROM students;

Output:

| avg_age |
|---------|
| 27.5    |

In calculations, SQL treats NULL values as unknown, so the average age is calculated based on known values only.

Conclusion


Understanding NULL values in SQL is crucial for querying and managing data effectively. By following this tutorial, you should now have a solid understanding of how to work with NULL values in SQL queries and handle them appropriately in your database operations.

Leave a Reply