Minus set operator
Hi All,
Is there minus set operator in informix 9.x. I want to do a
select * from x minus select * from y. is this allowed in informix 9.3. if nothow can we achieve it.
Bye.
Like everything else in SQL land, a MINUS can be performed a different way,
using a NOT EXISTS clause:
SELECT * from x
WHERE NO EXISTS (
SELECT 1 FROM y WHERE x.key1 = y.key1 and ...
);
Informix does not support MINUS directly.
Art S. Kagel
----- Original Message -----
From: Parameshwar.... <pcdudyala@yahoo.com>
At: 2/23 3:28
> Hi All,
> Is there minus set operator in informix 9.x. I want to do a
> select * from x minus select * from y. is this allowed in informix 9.3. if
not> how can we achieve it.
>
>
> Bye.
I just wanted to Add. A coworker (Zafar) and I found great performance gains using the MINUS function instead of NO EXISTS, it was day and night. Using the MINUS our performance was 80 percent or higher. The NO EXISTS performed very poorly with both versions, Informix 8.30 and Oracle 8i, this was back in 2002. As soon as Zafar, modified the code to use MINUS we were amazed of the performance gain. I then put in a request to Informix (2002) to have a MINUS function to be added within the Informix sql set, that did not happen.
A 'not exists' is a hash join. You have to have your engine set up properly to perform a hash join quickly. Not sure I remember how the minus is performed, although Oracle is sure a fan of indexed reads. j. ----- Original Message ----- From: <cberner99@juno.com> To: <ids@iiug.org> Sent: Thursday, February 24, 2005 10:53 AM Subject: Re: Minus set operator [4336] > > I just wanted to Add. > > A coworker (Zafar) and I found great performance gains using the MINUS function instead of NO EXISTS, it was day and night. Using the MINUS our performance was 80 percent or higher. The NO EXISTS performed very poorly with both versions, Informix 8.30 and Oracle 8i, this was back in 2002. As soon as Zafar, modified the code to use MINUS we were amazed of the performance gain. I then put in a request to Informix (2002) to have a MINUS function to be added within the Informix sql set, that did not happen. >