SELECT * FROM Employees WHERE NULL IS NULL; SELECT * FROM Employees WHERE NULL = NULL;

Question

I have recently started learning oracle and sql. While learning I encountered a couple of queries which my friend was asked in an interview.

SELECT * 
FROM Employees  
WHERE NULL IS NULL;

this query yields all the rows in the Employees table. As for as I have understood Oracle searches data in columns, so, NULL, is it treated as a column name here? Am I correct when I say that Oracle searches for data in columns? How come Oracle gives all the rows in this query? In WHERE clause, is it not must that the left hand side of a condition be a COLUMN NAME? Shouldn't it throw an error?

SELECT * 
FROM Employees 
WHERE NULL = NULL; 

gives NO ROWS SELECTED.

Well, I understand that I can not compare a NULL value using operators except IS NULL and IS NOT NULL. But why should it yield a result and not an error. Could somebody explain me this. Does Oracle treat NULL as a column as well as empty cells?

Answer 1

A where clause consists of conditional expressions. There is no requirement that a conditional expression consist of a column name on either side. In fact, although usually one or both sides are columns, it is not uncommon to have expressions that include:

• subqueries
• parameters
• constants
• scalar functions

One common instance is:

WHERE 1 = 1 AND . . .

This is a sign of automatically generated code. It is easier for some programmers to knit together conditions just by including AND <condition> but the clause needs an anchor. Hence, 1 = 1.

The way the WHERE clause works conceptually is that the clause is evaluated for each row produced by the FROM. If the clause evaluates to TRUE, then the row is kept in the result set (or for further processing). If it is FALSE or NULL, then the row is filtered out.

So, NULL IS NULL evaluates to TRUE, so all rows are kept. NULL = NULL evaluates to NULL, so no rows are kept.