Unleash the Power of Knowledge

NULLs affect IN and EXISTS

An IN is functionally equivalent to the = ANY clause , which means an OR Condition.

SQL> Select 1 From Dual where 1 in (1, NULL);1
———-
1

Because True or NULL will return True.
Same Exists is also same as IN..It returns True even if NULL is returned by the Subquery.Exists checks whether a row exists..it doesn’t check whether it is NULL or Not.

SQL> select ‘true’ val from dual where exists (select null from dual);VAL
—-
true

The difficult part comes when you use NOT IN.
The NOT IN clause is virtually equivalent to comparing each value with = and failing if any test is FALSE or NULL. It is equivalent to AND of all conditions, even if one of the condition is false or NULL, it wont return.

SQL> select ‘true’ from dual where 1 not in (null,2);no rows selected

if you understand the functionalities of IN , EXISTS and NOT IN then you can avoid common errors.

Undocumented Functions

sys_op_map_nonnull() is a undocumented Function to make NULL values equal.
It does not affect the Normal Comparisons.

SQL> Select 1 From Dual Where sys_op_map_nonnull(‘A’) = sys_op_map_nonnull(‘A’);1
———-
1

(Cont..)

Aggregate Functions

(Cont…..)

Logical Operation

When you use NULL in Logical Operations, it has some intersting result but logical.
Note : Boolean is not a Valid Data Type in SQL…it is valid only in PL/SQL

The Truth table is as follows

So Logically Thinking we can come to the conclusion why two of the above four is returning Boolean.

For example, false and null is false.
This makes sense because null, being an unknown value, could in this this context either be true or false.
False And => putting Unknown as True or False…the Result is always False
hence false and null is certainly false as well.

On the other hand, false or null is null because the result is true for false or true and false for false or false, hence the expression’s value is unknown, or null.

note: In the following Example, Since Boolean is not supported directly in SQL, i have manipulated it using Relational Operators.

In the above query, ‘1 = 1′ will return True and (1 > NULL) returns NULL ….since True or NULL is True,
whatever is there in the Select is returned.

SQL> Select ‘TRUE’ Res From Dual Where (1 = 1) OR (1 > NULL);

RES
—-
TRUE

SQL> Select ‘False’ Res From Dual Where Not ((1 > 1) and (1 > NULL));
The above query is for False and Null Returns False..so applying a NOT Operator will make it True and ouput is returned

RES
—–
False

NULL, the magic word in the database world. NULL means nothing or unknown.

It is not equal to space in case of varchar or zero in case of number.
Oracle treats the empty string (”) as null.

This is not ANSI SQL Standard.The length of an empty string(”) is null, not 0.

SQL> select length('') from dual;

LENGTH('')
----------

SQL> select NVL(to_char(length('')),'NULL') Len From Dual;

LEN
----
NULL

Arthimetic Operation

Any Arthimetic Operation(+,-,*,/) will return NULL if any one of the operand is NULL.

SQL> Select NVL(To_Char(1+2+3+4+5+NULL),'NULL') Sum from Dual;

SUM
----
NULL

Note :

NVL(NULL Value Handling function) is a Oracle Built in function to Convert NULL into some Meaning full value.
Example: NVL(expr1,expr2) => if expr1 is NULL then it Returns Expr2 Else it return expr1.

NVL2 is another function to Handle NULL values.
Example : NVL2(expr1,expr2,expr3) => if expr1 is NOT NULL then it returns expr2 else if expr1 is NULL then Returns expr3.

Relational Operator

Relational Operators (> , < , >= , <=, <>) will Return NULL if any one of the operand is NULL.so if you want to find the number of rows which has a particular column value as NULL. you cannot use ‘=’ operator to check. You need to use a Special operator ‘IS‘ to check for NULL values.

SQL> Select 1 From Dual Where NULL = NULL;

no rows selected

SQL> Select 1 From Dual Where NULL is NULL;

1
----------
1

SQL> Select count(*) from Emp where Comm is NULL;

Count
-----
8

You can generate a matrix table which shows all operations by the following code snippet,

SQL> Create Table Operand
2 (
3 num number
4 );

Table created.

Insert Non NULL and NULL Values into the table.

SQL> insert into operand values ( 11);

1 row created.

SQL> insert into operand values ( 42);

1 row created.

SQL> insert into operand values (null);

1 row created.

SQL> Commit;

Commit complete.

The Query to Generate the matrix is as follows

ANSI SQL Syntax :

Select op_left.num "op left",
case when op_left.num = op_right.num then ' = '
when op_left.num > op_right.num then ' > '
when op_left.num < op_right.num then ' < '
else ' ? '
end "rel",
op_right.num "op right"
From
operand op_left CROSS JOIN operand op_right;

The Output is as follows
op left rel op right
------- --- --------
11       =    11
11       <    42
11       ?   EMPTY
42       >    11
42       =    42
42       ?   EMPTY
EMPTY    ?    11
EMPTY    ?    42
EMPTY    ?   EMPTY

9 rows selected.

Deleting Files with a Pattern or Extension within a folderIf you wish to delete all files or a certain file within a folder and all subfolders,

1. simply go to Start -> Run -> “cmd.”
2. When the command prompt appears, go to the directory that you wish to delete from.
using cd command. For example

c:\>cd d:\kamal

3. Type in

D:\Kamal> DEL /F /S [*.type]

For example, if I wanted to delete all MP3 files from my PC, i would type

D:\Kamal> DEL /F /S *.mp3

 All of the MP3 files on my D:\Kamal folder will be deleted.

Note that if you do not want to delete files within subfolders, don’t type the “/S.”

               Everyone is interested in the result of an SQL statement, here we are going to see what is happening in the background when a SQL is executed.

                      During the parsing phase, Oracle will break down the submitted SQL statement into its component parts, determine what type of statement it is (Query, DML, or DDL), and perform a series of checks on it. The first two steps in parsing are

1. Syntax Check

             Oracle checks that the SQL statement is valid. it checks for the rightness of the keywords and position. For example, whether SELECT statement has FROM in right position?

2. Semantic Check

           Semantic Check checks if the statement is valid in the context of the objects in the database like Do the tables and columns referenced in the SQL statement actually exist in the database? Does the user executing the statement have access to the objects and are the proper privileges in place? Are there ambiguities in the statement? For example, consider a SQL Select statement “Select * from EMP123″, syntactically the statement is right, but if there is no table called EMP123, then it is semantically wrong. 

For example, the following SQL statement fails with a syntax error:

  SQL> select from where 4;
select from where 4
*
ERROR at line 1:   ORA-00936: missing expression

Here is an example of a SQL statement that fails with a semantic error:

  SQL> select * from table_doesnt_exist;
select * from table_doesnt_exist
*
ERROR at line 1:   ORA-00942: table or view does not exist