Home » SQL & PL/SQL » SQL & PL/SQL » Minus operator and NULL (11.2.0.3.0)
Minus operator and NULL [message #577078] Tue, 12 February 2013 03:32 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi Experts

I have a two tables with same column name , I wanted to find different record in table1 when compared with table2

create table table1(col1 number,col2 number,col3 number,col4 number,col5 number);

create table table2(col1 number,col2 number,col3 number,col4 number,col5 number);

insert into table1 values(1,2,NULL,NULL,NULL);

insert into table2 values(1,2,NULL,NULL,NULL);
commit;


select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);

no rows selected


Please tell me how come i get no rows selected when col3,col4,col5 is having null values but NULL could be anything so

NULL-NULL cannot be equal to zero how is it possible
Re: Minus operator and NULL [message #577081 is a reply to message #577078] Tue, 12 February 2013 03:34 Go to previous messageGo to next message
Littlefoot
Messages: 19788
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
MINUS is a set operator, not an arithmetic operator.
Re: Minus operator and NULL [message #577082 is a reply to message #577081] Tue, 12 February 2013 03:42 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Ok ,Just for my understanding
if col3,col4,col5 of table1 is null and col3,col4,col5 of table2 is null then

it will not report it as differnt records in table1 if we use below sql

select col1 from (select col1,col2,col3,col4,col5 from table1 minus select col1,col2,col3,col4,col5 from table2);


Thanks for your quick reply
Re: Minus operator and NULL [message #577084 is a reply to message #577082] Tue, 12 February 2013 04:03 Go to previous message
Michel Cadot
Messages: 59783
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, the operator is defined as it.

Regards
Michel
Previous Topic: DBMS Job (Wait for complete to go next process)
Next Topic: Problems with REPLACE( overreplacing
Goto Forum:
  


Current Time: Wed Nov 26 03:06:30 CST 2014

Total time taken to generate the page: 0.11203 seconds