Can we use '=' for comparing with NULL in sql

Question

I have table t1 as

  EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
------- ---------- --------- ---------- --------- ---------- ---------- ----------
   7369 SMITH      CLERK           7902 17-DEC-80        800                    20
   7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
   7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
   7566 JONES      MANAGER         7839 02-APR-81       2975                    20
   7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
   7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
   7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
   7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
   7839 KING       PRESIDENT            17-NOV-81       5000                    10
   7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
   7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

  EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
------- ---------- --------- ---------- --------- ---------- ---------- ----------
   7900 JAMES      CLERK           7698 03-DEC-81        950                    30
   7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
   7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
        NULL
        Null
        null
                   VP
-----------------------------------------------------------------------------

when I query

select * from t1 where ename=null

It returns "NO rows selected". As far as I know we cannot use '=' to compare with null. So why it is not showing error.

Answer 1

So why it is not showing error?

Because the syntax (of the expression) is correct.

However - as you already pointed out - even though the syntax of the expression is correct it will never be true

It's similar to writing where 1=2 - that is "correct" as well, but will never return any rows.

Answer 2

No you can't use = for null values because comparison with null values returns 'unknown' result and rows with 'unknown' result aren't included in result set. Use the query instead.

select * from t1 where ename is null

Answer 3

NULL in most flavors of SQL represents a value which is completely unknown. In this case, when comparing a column against NULL it cannot evaluate to TRUE because both values are completely unknown. Logically, comparing "I don't know" against "I don't know" does not evaluate to TRUE in SQL thinking.

One real-world analogy for the thinking of comparing NULL against NULL would be comparing two randomly-drawn playing cards against each other. We cannot say that there are equal or not equal, because we have never seen them.

Instead of directly comparing for a NULL value, try using the IS NULL construct:

SELECT * FROM t1 WHERE ename IS NULL

Answer 4

Of course you can use = to compare with null. The result is always going to be unknown, of course, but that's a value like any other. It isn't fundamentally different from doing 42 + null, or using any other operator. Would you really want to get an exception (or a syntax error) any time you're using null with some operator? That would kind of defeat the whole purpose of null, wouldn't it?

Of course, comparing something with a literal null is quite likely a mistake, and it might make sense for you to get a warning. But that would also mean that a literal should behave differently from a "real" value - and you really don't want that.

This is mostly analogous to how NaN works in floats. Any operation that involves a NaN results in NaN - except for isNaN, which is analogous to is null in SQL.

Answer 5

There is no error message because there is no error.

You can compare for equality to NULL. It just always returns UNKKNOWN/NULL. If you want to know whether ename is NULL then write ename IS NULL.

A SELECT can return no rows. A query just returns all the rows that are like what you asked. If none of them are like that then none are returned.

Answer 6

You have to use IS NULL instead of = to make this work.

SELECT * FROM t1 WHERE ename IS NULL

If you are using SQL Server, SET ANSI NULL OFF.
Set ANSI NULLS OFF will make NULL = NULL comparision return true

Also check this one In SQL Server, what does “SET ANSI_NULLS ON” mean?

When you compare two NULL expressions, the result depends on the ANSI_NULLS setting:

If ANSI_NULLS is set to ON, the result is NULL, following the ANSI convention that a NULL (or unknown) value is not equal to another NULL or unknown value.

If ANSI_NULLS is set to OFF, the result of NULL compared to NULL is TRUE.

Comparing NULL to a non-NULL value always results in FALSE2.

Answer 7

I suppose because Null means nothing, you are looking for some unknown value and get nothing. As written in T-SQL help:

To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.

So I think, because comparison operators return UNKNOWN it is not the error it is just behauviour of the SQL server.