Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Help with the in statement

3 posts in General Discussion Last posting was on 2004-07-22 13:38:26.0Z
SyBase Posted on 2004-07-17 22:43:59.0Z
From: "SyBase" <manny2463@yahoo.com>
Newsgroups: ianywhere.public.general
Subject: Help with the in statement
Lines: 15
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Original-NNTP-Posting-Host: 66stb62.codetel.net.do
Message-ID: <40f9ac91$1@forums-2-dub>
X-Original-Trace: 17 Jul 2004 15:47:45 -0700, 66stb62.codetel.net.do
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 17 Jul 2004 15:37:40 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 17 Jul 2004 15:43:59 -0700
X-Trace: forums-1-dub 1090104239 10.22.108.75 (17 Jul 2004 15:43:59 -0700)
X-Original-Trace: 17 Jul 2004 15:43:59 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3268
Article PK: 6722

I use the following sql

select * from customers
where cust_id in (select cust_id from Tran_table)

it returns the records I want but when I use the following sql using NOT IN

select * from customers
where cust_id not in (select cust_id from Tran_table)

I always get zero records even though there are
customers in the customers table that
are not in the Tran_table


Breck Carter [TeamSybase] Posted on 2004-07-18 13:40:59.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Help with the in statement
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <huukf0l3jfu0fm6fo3nd6l0g1to5h4ltge@4ax.com>
References: <40f9ac91$1@forums-2-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: adsl-10-44-254.mia.bellsouth.net
X-Original-Trace: 18 Jul 2004 06:44:46 -0700, adsl-10-44-254.mia.bellsouth.net
Lines: 28
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 18 Jul 2004 06:34:36 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 18 Jul 2004 06:40:59 -0700
X-Trace: forums-1-dub 1090158059 10.22.108.75 (18 Jul 2004 06:40:59 -0700)
X-Original-Trace: 18 Jul 2004 06:40:59 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3269
Article PK: 6723


On 17 Jul 2004 15:43:59 -0700, "SyBase" <manny2463@yahoo.com> wrote:

>I use the following sql
>
>select * from customers
>where cust_id in (select cust_id from Tran_table)
>
>it returns the records I want but when I use the following sql using NOT IN
>
>select * from customers
>where cust_id not in (select cust_id from Tran_table)
>
>I always get zero records even though there are
>customers in the customers table that
>are not in the Tran_table

Are you sure about that? Find one of the cust_id values that you are
sure isn't in the Tran_table and do this check:

SELECT * FROM Tran_table WHERE cust_id = xxx

Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Ani Nica Posted on 2004-07-22 13:38:26.0Z
From: "Ani Nica" <ani@sybase>
Newsgroups: ianywhere.public.general
References: <40f9ac91$1@forums-2-dub>
Subject: Re: Help with the in statement
Lines: 62
Organization: iAnywhere Solutions
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
NNTP-Posting-Host: anica-pc.sybase.com
X-Original-NNTP-Posting-Host: anica-pc.sybase.com
Message-ID: <40ffc352@forums-1-dub>
Date: 22 Jul 2004 06:38:26 -0700
X-Trace: forums-1-dub 1090503506 10.25.99.161 (22 Jul 2004 06:38:26 -0700)
X-Original-Trace: 22 Jul 2004 06:38:26 -0700, anica-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3292
Article PK: 7200

If the table Tran_table has a row with NULL value for the column
Tran_table.cust_id then the NOT IN predicate will be evaluated to UNKNOWN
for rows in the "customers" table for which there is no matching values in
"(select cust_id from Trans_table)". In the example below, the query Q1
returns no rows; Q2 returns one row '2'; Q3 returns '1'. Hence, you have to
rewrite your query to exclude the NULL values from the subquery to get the
result you are after. I.e.,
select * from customers
where cust_id not in (select cust_id from Tran_table where
trans_table.cust_id IS NOT NULL)

For more info on ANY and ALL predicates, please see the section: ASA SQL
User's Guide Using Subqueries "Quantified comparison tests with ANY and ALL"


Example:
create table T1( id int not null);
create table T2( id int);

insert into T1 values( 1);
insert into T1 values( 2);
insert into T2 values( 1);
insert into T2 values( NULL );

Q1:
select * from T1
where id not in (select id from T2);


Q2:
select * from T1
where id not in (select id from T2 where id IS NOT NULL);

Q3:
select * from T1
where id in (select id from T2);

--
Ani Nica
Research and Development, Query Processing
iAnywhere Solutions Engineering

"SyBase" <manny2463@yahoo.com> wrote in message
news:40f9ac91$1@forums-2-dub...
> I use the following sql
>
> select * from customers
> where cust_id in (select cust_id from Tran_table)
>
> it returns the records I want but when I use the following sql using NOT
IN
>
> select * from customers
> where cust_id not in (select cust_id from Tran_table)
>
> I always get zero records even though there are
> customers in the customers table that
> are not in the Tran_table
>
>