I understand that the SQL standard allows multiple NULL values in a column that is part of the UNIQUE constraint.
What I don't understand is why the UNION construct (at least in PostgreSQL,) treats NULL values as the same. For example:
$ select * from tmp_a;
a | b
---+---
a | b
a |
|
(3 rows)
$ select * from tmp_b;
a | b
---+---
a | c
a |
|
(3 rows)
$ select a, b from tmp_a union select a, b from tmp_b order by 1, 2;
a | b
---+---
a | b
a | c
a |
|
(4 rows)
I couldn't find a more primary source, but according to this Wikipedia article, there is a special case for NULLs when it comes to grouping operations. For those operations, like DISTINCT and UNION, NULL is "not distinct" from NULL, even though the two NULLs are also "not equal".
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With