general clarifications!!urgent [message #35723] |
Wed, 10 October 2001 05:48 |
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 |
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.
----------------------------------------------------------------------
|
|
|