CODEWITHSUNDEEP
sqldatabasepostgresqlnullunion
niodev
niodev

Reputation: 39

SQL: dealing with NULL values (union)

Given this two, similar, queries: 

SELECT EXTRACT(YEAR FROM to_date) AS year_expired
        FROM     salaries
        GROUP BY year_expired
        ORDER BY year_expired

and

(
SELECT EXTRACT(YEAR FROM to_date) AS year_expired
          FROM salaries
          WHERE EXTRACT (YEAR FROM to_date) > 1990
          GROUP BY year_expired
          ORDER BY year_expired
)
UNION
(
SELECT EXTRACT(YEAR FROM to_date) AS year_expired
          FROM salaries
          WHERE EXTRACT (YEAR FROM to_date) <= 1990
          GROUP BY year_expired
          ORDER BY year_expired
)

The output in the first query gives a row with NULL value into the year_expire field, while in the second there is no NULL row.

You can see the example here.

I'm asking myself why this happens. Does the GROUP BY also takes NULL values? I know that the UNION threats the table as a set, and thus it doesn't select duplicates.

How does UNION handle NULL values, and how does GROUP BY handle NULL values?

EDIT: I changed the second query (the second column was wrong)

EDIT2: Thanks for the answer, but could you explain what's the difference? I thought that it might be because of the WHERE CLAUSE: with the comparison if we have a NULL value it becomes UNKNOWN and that's why the WHERE clause doesn't take into account the NULL.

Could you tell me at least if what I said is what happens?

PS: I'm using PostgresSQL.

Upvotes: 1

Views: 5952

Answers (2)

Shrashti
Shrashti

Reputation: 150

You are using comparison operators >, <= in second query and no filters in first query. It won't consider null > 1990 or null <= 1990 and will exclude these records.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

This has nothing to do with the UNION. You are filtering out the NULL values with the WHERE clauses.

The two queries are not identical.

Upvotes: 3

Related Questions