Re: Dumb Oracle Question

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 04 Jun 2008 16:00:52 -0700
Message-ID: <1212620450.314997@bubbleator.drizzle.com>


Mtek wrote:
> Hi,
>
> Aren't these statements supposed to be equal??
>
> select cust_no from customer where z_rank_d = 1
> minus
> select cust_no from customer_rank1;
>
>
>
> select cust_no from customer where z_rank_d = 1
> and not exists
> (select cust_no from customer_rank1);
>
> What am I missing? The first returns 27 rows, the second,
> nothing.......
>
> John

What you are missing is that your subquery is uncorrelated.

The syntax you are looking for is a derivative of this: SELECT srvr_id
FROM servers s
WHERE EXISTS (
   SELECT srvr_id
   FROM serv_inst i
   WHERE s.srvr_id = i.srvr_id);

Note the second WHERE clause.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Jun 04 2008 - 18:00:52 CDT

Original text of this message