SQL: Select (null = null);

Question

This question is out of a databases exam i was looking at, so it might be nothing one can ever use in a real situation...

What output does the following valid SQL statement produce? Explain your answer!

SELECT (NULL = NULL);

I can easily produce the output of the statement, which is

school=> select (null = null);
 ?column?
----------

(1 row)

in psql 8.4.11, but how and why this is the answer i don't know... I would have guessed it tries to evaluate the expression inside the brackets and comes up with true/false but apparently it doesn't.

Any hints on why it behaves like it does?

Thanks

To clarify my question: It looks like the result is 1 empty row. I can't / don't want to change my query statement as its given by the exam excercise. But where does it come from? Why isn't the result empty... ? — tannerli
– tannerli, Commented Aug 24, 2012 at 11:02
@nonnb: No. null != null (or to use the inequality operator specified in the SQL standard, null <> null) is also neither true nor false, since you can't say that two unknown values are known to be not equal. See @Oded's answer. — kgrittn
– kgrittn, Commented Aug 24, 2012 at 12:10
allow me to extends this question... what about select ARRAY[null]::bigint[] && ARRAY[null]::bigint[] ? — Victor
– Victor, Commented Feb 12, 2019 at 18:16

Oded · Accepted Answer · 2012-08-24 11:05:40Z

10

NULL stands for "Unknown Value". It is not known whether a value is true or false or anything else.

So, when comparing two unknown values, what would the answer be? Is UnknownA equal to UnknownB?

Answer? Unknown...

Here is an example for SQL Server:

IF (NULL = NULL)
  PRINT 'Equals'

IF (NULL != NULL)
  PRINT 'Not Equals'


IF (NULL IS NULL)
  PRINT 'IS'

IF (NULL IS NOT NULL)
  PRINT 'IS NOT'

The only thing that gets printed: IS

edited Aug 24, 2012 at 11:05

answered Aug 24, 2012 at 10:58

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

juergen d Over a year ago

My understanding of the question was: Why can't I do something like select 1 = 1 to produce trueor false

valex Over a year ago

+1 Also if one value is known select (1=null) gives NULL answer

Dmytro Shevchenko Over a year ago

@juergend you can, if you're not using MS SQL.

Oded Over a year ago

@juergend - The question is specifically about NULL behavior.

tannerli Over a year ago

So it actually DOES evaluate the expression, but the result is NULL again makes sense

|

Dmytro Shevchenko · Accepted Answer · 2012-08-24 10:59:23Z

1

I suppose, the expected answer is NULL. That's what MySQL does. That's what PostgreSQL returns as well, actually. Probably, your SQL client just doesn't show the single row where the only returned value is a NULL.

MS SQL server doesn't have a boolean type, though, so it cannot just select a result of boolean expression. So the result in MS SQL is an error.

The point of the question is to check your understanding of NULL logic in SQL. Instead of =, you should use the IS operator when comparing to NULL (unless the ANSI_NULLS option is set to true in MySQL).

answered Aug 24, 2012 at 10:59

Dmytro Shevchenko

34.9k6 gold badges56 silver badges68 bronze badges

5 Comments

Oded Over a year ago

You could use IF (NULL = NULL).

Dmytro Shevchenko Over a year ago

@Oded This would return false.

Oded Over a year ago

As would IF (NULL != NULL).

Dmytro Shevchenko Over a year ago

Ah, you probably meant, in MS SQL server. You should've specified that.

kgrittn Over a year ago

@Shedal: The question is flagged as being about PostgreSQL, not SQL Server. The PostgreSQL behavior conforms to what is required by the standard, so it should generalize to some degree....

Collectives™ on Stack Overflow

SQL: Select (null = null);

2 Answers 2

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related