Forem: Sachi Goto

The Intricacies of Null Values in MySQL

Sachi Goto — Sun, 12 May 2024 13:20:29 +0000

Introduction:

For those who are new to MySQL, including myself, you might encounter unexpected results when you query from a table that has some null values. In MySQL, null is neither true nor false but unknown, and you can't compare null with null unless you use the 'IS NULL' or 'IS NOT NULL' operator. I found that the way MySQL treats null is not only interesting but also important to understand. Here, I'm demonstrating how null behaves using various MySQL functions!

Joining Tables with Columns That Contain Null Values

NULL with INNER JOIN vs. NULL with LEFT JOIN
Let's say there are two tables, as described below:

Table name : teachers

id	dept_id	name
101	1	Shrivell
102	1	Throd
103	1	Splint
104	NULL	Spiregrain
105	2	Cutflower
106	NULL	Deadyawn

Table name : departments

id	name
1	Computing
2	Design
3	Engineering
NULL	NULL

Notice that there are null values in the teachers table. What would happen if I use an INNER JOIN to combine these two tables?

SELECT teachers.name, departments.name AS department
FROM teachers INNER JOIN departments
ON (teachers.dept_id = departments.id)

Here is the result

+-----------+------------+
| Name      | Department |
+-----------+------------+
| Shrivell  | Computing  |
| Throd     | Computing  |
| Splint    | Computing  |
| Cutflower | Design     |
+-----------+------------+

Notice that in the results, you don't see the names of two teachers whose department_ids are null.

But what about using a LEFT JOIN? Let's take a look.
Here is the query where I simply swap 'INNER' for 'LEFT'.

SELECT teachers.name, departments.name AS department
FROM teachers LEFT JOIN departments
ON (teachers.dept_id = departments.id)

Here is the result

+------------+------------+
| Name       | Department |
+------------+------------+
| Shrivell   | Computing  |
| Throd      | Computing  |
| Splint     | Computing  |
| Spiregrain | NULL       |
| Cutflower  | Design     |
| Deadyawn   | NULL       |
+------------+------------+

You'll notice that all the teachers are listed. INNER JOIN does not return records with null values, but LEFT JOIN does.

WHY?

Keep in mind that when using the equality operator (=) with NULL to NULL, the result is neither true nor false—it's unknown. Therefore, records with NULL values on both sides of the comparison are not included in the result set when using INNER JOIN. However, by the nature of LEFT JOIN, it selects ALL records from the left side table (which is the teachers table in this case), even if the dept_id is null. Unlike INNER JOIN, which would remove such records from the list, LEFT JOIN retains them.

EXISTS & NULL and IN & NULL

Both EXISTS and IN do not return records with null values.
Let's consider the example where I have these tables.

Table name: customers

customer_id	first_name	last_name	phone_number
1	John	Doe	123-456-7890
2	Jane	Smith	987-654-3210
3	Alice	Johnson	NULL
4	Bob	Brown	555-555-5555

Table name: customers_2

customer_id	first_name	last_name	phone_number
1	John	Doe	123-456-7890
2	Jane	Smith	NULL
3	Alice	Johnson	111-222-3333
4	Bob	Brown	NULL

Here is a query:
Using EXISTS to check if a name in b_table exists in a_table.

SELECT * FROM customers AS c1
WHERE EXISTS(
SELECT * FROM customers_2 AS c2
WHERE c1.first_name = c2.first_name 
AND c1.last_name = c2.last_name 
AND c1.phone_number = c2.phone_number);

Using IN to see if name in b_table is in a_table

SELECT *
FROM customers AS c1
WHERE (c1.first_name, c1.last_name, c1.phone_number) IN (
    SELECT c2.first_name, c2.last_name, c2.phone_number
    FROM customers_2 AS c2
);

Both returns the same result

+-------------+------------+-----------+--------------+
| customer_id | first_name | last_name | phone_number |
+-------------+------------+-----------+--------------+
| 1           | John       | Doe       | 123-456-7890 |
+-------------+------------+-----------+--------------+

Notice that records with null values are not returned.

WHY?
Because with EXISTS, you can't link columns that are null. For IN, it ignores null values as if they don't exist. Thus, both treat nulls in the same way: neither returns nulls.

** HOWEVER, it's not the same story with NOT EXISTS and NOT IN when dealing with NULL!**

Let's take a look.
I simply swapped EXISTS for NOT EXISTS.

SELECT * FROM customers AS c1
WHERE NOT EXISTS(
SELECT * FROM customers_2 AS c2
WHERE c1.first_name = c2.first_name 
AND c1.last_name = c2.last_name 
AND c1.phone_number = c2.phone_number);

Here is the result

| customer_id | first_name | last_name | phone_number  |
|-------------|------------|-----------|---------------|
| 2           | Jane       | Smith     | 987-654-3210  |
| 3           | Alice      | Johnson   | [NULL]        |
| 4           | Bob        | Brown     | 555-555-5555  |

Notice that it is returning records with null values. Both Jane and Bob's phone numbers in the customers_2 table are null, but since both phone numbers have values in the customers table, the returned values are not null.

How about using NOT IN?

SELECT *
FROM customers AS c1
WHERE (c1.first_name, c1.last_name, c1.phone_number) NOT IN (
    SELECT c2.first_name, c2.last_name, c2.phone_number
    FROM customers_2 AS c2
);

Here is the result

+-------------+------------+-----------+--------------+
| customer_id | first_name | last_name | phone_number |
+-------------+------------+-----------+--------------+

Nothing is returned!
Why does NOT EXISTS return null values but NOT IN does not? It's because they both treat null differently. NOT EXISTS is simply the opposite of EXISTS. It returns EVERYTHING that is not returned by EXISTS, including nulls.

However, NOT IN returns records that evaluate to TRUE, but in MySQL, null is neither TRUE nor FALSE—it is unknown.

Let's take a look at the record for Jane Smith with Id2.
When NOT IN is used, it compares:
Jane != Jane OR Smith != Smith OR 987-654-3210 != null.

Jane != Jane -> False
Smith != Smith -> False
987-654-3210 != null -> NULL
False or False or Null evaluates to NULL, so the record doesn't get returned!

Here's a little twist:
What if the first name in the customers table does not match the first name in the customers_2 table?

Let’s compare:
Jane Smith 987-654-3210 (from the customers table) and Jack Smith null (from the customers_2 table).

Here is the result

+-------------+------------+-----------+--------------+
| customer_id | first_name | last_name | phone_number |
+-------------+------------+-----------+--------------+
| 2           | Jack       | Smith     | 987-654-3210 |

What? You see Jack! Doesn't NOT IN fail to evaluate null as either false or true, so the record shouldn't be returned?

Let's analyze it:

Jane != Jack -> True
Smith != Smith -> False
987-654-3210 != null -> NULL

True or False or Null actually returns:
True || False -> True
True || NULL -> True
Therefore, the record gets returned!

If you want to understand how true, false, and null are evaluated, here is the link:

https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html#operator_and

As you can see, it can get quite confusing when it comes to comparing null values.

At the end of the day, I think it's best to explicitly compare null values using IS NULL or IS NOT NULL, which returns true or false as shown below.

SELECT * FROM customers AS c1
WHERE EXISTS(
SELECT * FROM customers_2 AS c2
WHERE c1.first_name = c2.first_name 
AND c1.last_name = c2.last_name 
AND c1.phone_number = c2.phone_number OR  (c1.phone_number IS NULL AND c2.phone_number IS NULL));

Conclusion

Handling null values in MySQL can be quite challenging, requiring careful attention when they are present in your database. It's crucial to conduct explicit null checking to clearly define how null values should be treated. By implementing the strategies I mentioned earlier, such as using IS NULL or IS NOT NULL, you can ensure more predictable and accurate query results.

Type Aliases VS Interfaces in Typescript

Sachi Goto — Sun, 26 Mar 2023 10:16:23 +0000

Type aliases and interfaces are similar in a way that both are used to define object shapes. However, they have some notable differences that make them suitable for different scenarios. Understanding these differences can help you make decisions on when to use each one!

Here is an example of an interface and a type alias.

// Interface
interface Profile{
       name:string;
       age:number;
       method(): string;


// Type Aliases
type Profile = {
       name:string;
       age:number;
       method(): string;
}

They look almost identical. So What are the differences?

Difference 1 : extends vs intersection
Similar to how the extends keyword is used in class declarations in object-oriented programming, interfaces can extends from another interface using the "extends" keyword.

This is how it works.


interface Profile{
       name:string;
       email:string;
       age:number;  
}

interface MoreProfile extends Profile{
   phone:number;
   greeting(): string;
} 

const steve:MoreProfile ={
    name:'Steve',
    email:'steve@gmail.com',
    age:20,
    phone:123,
    greeting(){
          return  'Hello!'
    }
}

The MoreProfile interface inherits from the Profile interface. It means that if you use the MoreProfile interface for a variable, you will need to include all the properties from both the Profile and MoreProfile interfaces. This feature is useful and intuitive, as the extends keyword clearly signifies hierarchical relationships between interfaces. Neat, right?

*How about Type Aliases? *

Type aliases have a similar feature to extends called "intersection" to combine multiple types. Basically, you can use the ampersand (&) to add another type to the alias.

Here is an example


type Profile = {
       name:string;
       email:string;
       age:number;     
}

type MoreProfile =  {
   phone:number;
   greeting(): string;
} 

const steve:Profile & MoreProfile ={
    name:'Steve',
    email:'steve@gmail.com',
    age:20,
     phone:123,
     greeting(){
          return  'Hello!'
     }

Also, you can mix and match interfaces and type aliases.

// adding a type alias to interface

type Profile = {
       name:string;
       email:string;
       age:number;  
}

interface MoreProfile extends Profile  {
    phone:number;
   greeting(): string;
}


const steve:MoreProfile = {
  name:'steve',
  email:'steve@gmail.com',
  age:20,
  phone:123,
  greeting(){
    return 'hello'
  }

}

// Adding interface to a type aliase 

interface Profile {
       name:string;
       email:string;
       age:number;  
}


type MoreProfile = Profile & {
    phone:number;
   greeting(): string;
}


const steve:MoreProfile = {
  name:'steve',
  email:'steve@gmail.com',
  age:20,
  phone:123,
  greeting(){
    return 'hello'
  }


}

Keep in mind that interfaces cannot extend type aliases with union types, as I will explain later in the third difference: Union Types

I don't recommend mixing and matching interfaces and type aliases. It's better to maintain consistency and use one approach throughout your codebase to avoid confusion and inconsistency.

Difference 2 : Interface Merging

Interface merging is a unique feature that type aliases do not have. However, it is not recommended to rely heavily on this feature as it can make the code harder to maintain and understand.

One use case of interface merging is to create an interface that describes the entire API. This can be achieved by creating an empty api interface at the parent level and adding the api interface that describes the API structure in its child components/modules. This approach ensures that each module is responsible for creating its own API interface, which promotes better organization and modularity in the codebase.

Here is how it works


interface Profile{
       name:string;

}

interface Profile{
  email:string;
}

const steve:Profile = {
    name:'steve',
    email:'steve@gmail.com'

}

// if email is missing, it will throw you an error:
// Property 'email' is missing in type '{ name: string; }' but required in type 'Profile'.(2741)

// You can not do this with type aliases 

type Profile = {
   name:string;
}

type Profile ={
   email:string;
}

//error Duplicate identifier 'Profile'.(2300)

const steve:Profile = {
    name:'steve',
    email:'steve@gmail.com'

}

//error Type '{ name: string; email: string; }' is not assignable to type 'Profile'.
  Object literal may only specify known properties, and 'email' does not exist in type 'Profile'.(2322)

Difference 3 : Union Types
Interfaces do not accept union types, while type aliases do. This is because interfaces can only accept statically known members, meaning that they cannot accept objects that may potentially change.

In other words, interfaces require all properties to be explicitly defined at the time of declaration, while type aliases allow for more flexibility by accepting union types and other dynamic properties.

Here is the example


type Profile = {name:string} | {name:number}

interface MoreProfile extends Profile  {
    phone:number;
   greeting(): string;
}

// error An interface can only extend an object type or intersection of object types with statically known members.(2312)

In this example, since Profile is union type, the shape of the Profile object can be wither name:string or name:number which means it's not static so it will throw you an error!

If you use union types a lot in your code base, it would be better to use types because interface won't accept union types.

Difference 4 Tuples
Tuples can be declared in Type Aliases but for interfaces, you can't unless you declare a tuple inside an interface.

Here is the example below.


type Profile = [name:string, age:number]
const typeAliaseProfile:Profile = ['Steve', 20] 

// declare tuples only inside of the interface 
interface Profile { profile:[string, number]}
const interfaceProfile:Profile = {profile:['Steve', 20]}

Summary

Interfaces and type aliases both used to defined the shape of an object. Both take option using a question mark and readonly features.

Interface only - Interface merging and extends

Type Aliases only - Union types, Tuple (interface takes tuples only within an interface), intersection

Conclusion

If you are familiar with OOP, using interface might be intuitive for you. If you use union types often in your base code, using type aliases make sense.
Whichever you choose, it's important to be consistent.

Forem: Sachi Goto

The Intricacies of Null Values in MySQL

Introduction:

Conclusion

Type Aliases VS Interfaces in Typescript

Summary

Conclusion

Resources