Home » SQL & PL/SQL » SQL & PL/SQL » general clarifications!!urgent
general clarifications!!urgent [message #35723] Wed, 10 October 2001 05:48 Go to next message
sini
Messages: 10
Registered: October 2001
Junior Member
hi
1. would like to know what is the difference
between using NOT IN and MINUS
when selecting from 2 tables

e.g:
(SELECT name
FROM a1)
MINUS
(SELECT name
FROM a2)

is the same as

(SELECT name
FROM a1)
NOT IN
(SELECT name
FROM a2)

could anyone tell me what is the differnce
between the 2 queries??

2.when do i need to use the datatype
BINARY_INTEGER?could it be replaced for INTEGER
or NUMBER datatype?

if anyone knows the answer,please respond

thanks
sini

----------------------------------------------------------------------
Re: general clarifications!!urgent [message #35724 is a reply to message #35723] Wed, 10 October 2001 08:53 Go to previous message
Jon
Messages: 483
Registered: May 2001
Senior Member
In the example cited, there is no difference in the result set returned. However, MINUS must have the same number of columns. NOT IN does not have that restriction so you could say

SELECT name, address FROM A1 where name NOT IN
(Select name from B1);

However, this is not very efficient. A better way would be to

SELECT a1.name, a1.address FROM a1
WHERE NOT EXISTS
(select 'x' from b1
where a1.name = b1.name);

2. BINARY_INTEGER is generally more efficient and reduces implicit data casting.

----------------------------------------------------------------------
Previous Topic: retrieving more than one row
Next Topic: Re: ORA-00600: internal error code, arguments: [15267], [223], [], [], [], [], [], []
Goto Forum:
  


Current Time: Sat May 25 08:56:33 CDT 2024