Re: NOT IN with a subquery

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 01/20/05 

Date: Thu, 20 Jan 2005 23:03:59 -0000

Louis,

Small correction: If there is a NULL in the list for the IN clause, the IN
clause will evaluate to UNKNOWN for all the values that are not otherwise in
the list, not to FALSE.

IF 1 NOT IN (NULL) OR 1 IN (NULL)
SELECT 'FALSE OR TRUE'
ELSE SELECT 'UNKNOWN' 

-- 
Jacco Schalkwijk
SQL Server MVP
"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message 
news:ehXJ0A0$EHA.1908@TK2MSFTNGP15.phx.gbl...
> NULLs.  If source is NULL then the IN clause will be NULL, which is 
> evaluated to FALSE.  Do you have null values for source?
>
> Probably need to change to:
>
> select distinct dp_source
> from clients
> where dp_source NOT IN(select distinct source from applicat)
> or dp_source is null
>
> -- 
> ----------------------------------------------------------------------------
> Louis Davidson - drsql@hotmail.com
> SQL Server MVP
>
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design - 
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in 
> consulting services.  All other replies may be ignored :)
>
> "news.microsoft.com" <Darwin@DotNetDesigns.com> wrote in message 
> news:Ozn1l5z$EHA.1524@TK2MSFTNGP09.phx.gbl...
>> This query returns 155 rows:
>> select distinct dp_source from clients
>>
>> This query returns 97 rows:
>> select distinct dp_source from clients where dp_source IN(select distinct 
>> source from applicat)
>>
>> This query returns 0 rows:
>> select distinct dp_source from clients where dp_source NOT IN(select 
>> distinct source from applicat)
>>
>> If there are 155 rows and 97 are IN my subquery, I would expect 58 rows 
>> NOT IN my subquery....
>>
>> What am I missing?
>>
>> Thanks,
>> Darwin
>>
>
>

Relevant Pages

  • Re: Query based on other queries problem
    ... Not enough details to really propose a solution, but you might be able to use sub-queries in a where clause to solve your problem of the query not being updatable. ... EventClients table contains a list of clients for an event ... I receive orders from my clients that contain personal data about people. ...
    (microsoft.public.access.queries)
  • Re: Query based on other queries problem
    ... I receive my orders from the clients, ... I have tried something very similar to your proposed solution, and the query ... inside the WHERE clause. ... Create a query that retrieves the first Customer for each order, ...
    (microsoft.public.access.queries)
  • Re: [PHP] problems in WHERE statment
    ... Connected successfullyCould not successfully run query from DB: Unknown ... column '$today' in 'where clause' ...
    (php.general)
  • Re: Extracting one out of multiple query advise
    ... John Spencer wrote: ... I believe Marshall meant to use T.DateCreated in the Where clause of the ... main query instead of T.Company. ... FROM clients As T ...
    (microsoft.public.access.queries)
  • RE: Any good T-SQL quick reference recommended?
    ... The full syntax of the SELECT ... SELECT Clause ... Specifies the columns to be returned by the query. ... Specifies that duplicate rows can appear in the result set. ...
    (microsoft.public.sqlserver.programming)