|
|
Posted by Tom Moreau on 05/04/06 01:28
I'm a big EXISTS fan. The NULLs here are to do with the column you chose in
your subquery. I wouldn't think of it like COALESCE. Basically, NULL <>
anything, even another NULL. An IN predicate can be broken down like this:
x IN (1, 2, 3 null)
.... means:
x = 1 or x = 2 or x = 3 or x = null
So, if x is 1, 2 or 3, it will be true. If x is null, then the result is
false, since x is really unknown and not equal to anything.
Now consider this:
x NOT IN (1, 2, 3 null)
.... means:
x <> 1 and x <> 2 and x <> 3 and x <> null
Google de Morgan's Law.
What if x is 4? All conditions must be met. It passes the first 3, but
fails on the last, since 4 <> null is unknown, and is treated as false.
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@gmail.com> wrote in message
news:1146665188.587472.63170@v46g2000cwv.googlegroups.com...
Hey, whoa, that works.
You're the man Tom.
So just use EXISTS when there are nulls in the select list?
Kinda like coalesce, but for subqueries?
Tom Moreau wrote:
> Sounds like you have nulls in key2_value. Use WHERE NOT EXISTS, instead
> of
> WHERE ... NOT IN.
>
> select
> *
> from
> Filenametbl f
> where not exists (select * from
> document_link_staging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_storage dls
> where dls.key2_value = f.pickno)
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@gmail.com> wrote in message
> news:1146621327.262902.130050@v46g2000cwv.googlegroups.com...
> So I've set up 3 tables for some recursive data verification for an
> object link embedding procedure.
> Basically importing filenames.
>
>
> The three fields in question are identical.
>
> Document_link.key2_value
> document_link_staging.key2_value
> document_link_storage.key2_value
>
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
>
> Filenametbl.pickno
>
> here is the rub.
>
> If I have 100 identical records in the document link tables with
> key2_values that are in Filenametbl, and three hundred records in
> Filenametbl, then this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging) and pickno not in (select key2_value from
> document_link_storage)
>
> should return 200 records.
> It returns 0 records.
>
> So while this query:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_staging)
>
> returns 200 records in this scenario,
>
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_storage)
>
> I am trying to figure out why that is, as the casting for the
> key2_values is exactly the same (varchar(255))
>
> Can anybody tell me how to remedy this sort of thing, as its been
> bugging me for about 2 months.
> I've been able to work around it, but what it is... is just terribly
> ineffiecient.
Navigation:
[Reply to this message]
|