SQL: select (null = null);

Question

This question comes out of the database exam that I was looking at, so in a real situation nothing can happen ...

What output creates the next valid SQL statement? Explain your answer!

SELECT (NULL = NULL);

I can easily get the output of the instruction, which

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

 (1 row)

in psql 8.4.11, but how and why is it an answer that I don’t know ... I would have guessed that it was trying to evaluate the expression inside the brackets and coming up with true / false, but apparently t.

Any hints on why he is acting as if this is happening?

thanks

Answer 1

NULL means "Unknown value". It is not known whether the meaning is true or false or something else.

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

Answer? Unknown...

Here is an example of 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 printed: IS

Answer 2

I assume the expected response is NULL . This is what MySQL does. That PostgreSQL returns as well, actually. Your SQL client probably just isn't showing a single row, where the only return value is NULL .

MS SQL server does not have a boolean type, so it cannot just select the result of a boolean expression. Thus, the result in MS SQL is an error.

The purpose of the question is to test your understanding of the NULL logic in SQL. Instead of = you should use the IS operator when comparing with NULL (unless the ANSI_NULLS parameter is set in MySQL).