To Compare “NULL” Values Use “NVL” Function in Oracle

Query to compare “Null” values in oracle tables:

Step1: Create a Table “Test1” as shown below:

CREATE TABLE TEST1 (A NUMBER,B NUMBER);

Step 2: Insert few random data into “TEST1” Table.

INSERT INTO TEST1 VALUES (1,25);

INSERT INTO TEST1 VALUES (2,20);

INSERT INTO TEST1 VALUES (3,NULL);

INSERT INTO TEST1 VALUES (4,30);

INSERT INTO TEST1 VALUES (5,NULL);

Step 3: Select the “TEST1” Table.

SELECT * FROM TEST1;

A	B
1	25
2	20
3
4	30
5

Step4: Create a Table “Test2” as shown below:

CREATE TABLE TEST2 (A NUMBER,B NUMBER);

Step 5: Insert few random data into “TEST2” Table.

INSERT INTO TEST2 VALUES (1,25);

INSERT INTO TEST2 VALUES (2,NULL);

INSERT INTO TEST2 VALUES (3,20);

INSERT INTO TEST2 VALUES (4,NULL);

INSERT INTO TEST2 VALUES (5,NULL);

Step 6: Select the “TEST2” Table.

SELECT * FROM TEST2;

A	B
1	25
2
3	20
4
5

The column “A” of table “TEST1” and “TEST2” is same and can be act as a joining condition for both tables.

Run the below query to fetch column B’s data from both the table that is not equal.

SELECT TEST1.A,TEST1.B,TEST2.B FROM TEST1,TEST2 WHERE TEST1.A=TEST2.A AND TEST1.B!=TEST2.B

Output: No Rows Returned. (It is because null values cannot be equal to or unequal to any value)

Now, run the below query to fetch the actual result using NVL function.

SELECT TEST1.A,TEST1.B,TEST2.B FROM TEST1,TEST2 WHERE TEST1.A=TEST2.A AND NVL(TEST1.B,0)!=NVL(TEST2.B,0)

Expected Output:

A	B	B_1
2	20
3		20
4	30