Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In PostgreSQL (and maybe other engines), why does the UNION statement consider NULL values the same, while the UNIQUE constraint does not?

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)
like image 206
Shankster Avatar asked Apr 20 '11 02:04

Shankster


1 Answers

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".

like image 181
Brian L Avatar answered Sep 20 '22 13:09

Brian L