When doing an INNER JOIN, are columns matched on NULL values?


#1

Question

In the context of this exercise, when doing an INNER JOIN, are columns matched on NULL values?

Answer

No, when you have NULL values in a column, these are never matched to other NULL values. This is because NULL signifies the absence of any value, and cannot be compared as they will never equal anything. Doing say NULL = NULL results in False.

Let’s take for example the animation given in the exercise, which shows how INNER JOIN works. Let’s say that an additional row was added to each table, with NULL in the C2 column, such that they become

Left table:

C1, C2
A, B
Q, W
X, Y
T, NULL

Right table:

C2, C3
B, C
E, R
Y, Z
NULL, V

If we inner joined these tables the same way, we would end up with the same result, because NULL values are not matched.

C1, C2, C3
A, B, C
X, Y, Z