An odd quandry.

**Tom Moreau** · May 3 '06, 02:35 AM

Re: An odd quandry.

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_s taging dls
where dls.key2_value = f.pickno)
and not exists (select * from
document_link_s torage dls
where dls.key2_value = f.pickno)

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<KyussWren@gmai l.com> wrote in message
news:1146621327 .262902.130050@ v46g2000cwv.goo glegroups.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.k ey2_value
document_link_s taging.key2_val ue
document_link_s torage.key2_val ue

And these three fields are populated from a substring of the filenames
which are generated in another table.

Filenametbl.pic kno

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_s taging) and pickno not in (select key2_value from
document_link_s torage)

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_s taging)

returns 200 records in this scenario,

this query returns 0:
select * from Filenametbl where pickno not in (select key2_value from
document_link_s torage)

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.

**KyussWren@gmail.com** · May 3 '06, 02:15 PM

Re: An odd quandry.

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:[color=blue]
> 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_s taging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_s torage dls
> where dls.key2_value = f.pickno)
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@gmai l.com> wrote in message
> news:1146621327 .262902.130050@ v46g2000cwv.goo glegroups.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.k ey2_value
> document_link_s taging.key2_val ue
> document_link_s torage.key2_val ue
>
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
>
> Filenametbl.pic kno
>
> 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_s taging) and pickno not in (select key2_value from
> document_link_s torage)
>
> 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_s taging)
>
> returns 200 records in this scenario,
>
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_s torage)
>
> 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.[/color]

**Erland Sommarskog** · May 3 '06, 09:55 PM

Re: An odd quandry.

(KyussWren@gmai l.com) writes:[color=blue]
> 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?[/color]

EXISTS and NOT EXISTS have wider applicability than so. You also need
EXISTS / NOT EXISTS when the condition involves more than one column.
IN + subquery is mainly something I use when I'm writing ad hoc-queries
and I'm lazy. In programming code I use EXISTS / NOT EXISTS 90% of
the time.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Tom Moreau** · May 3 '06, 10:55 PM

Re: An odd quandry.

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@gmai l.com> wrote in message
news:1146665188 .587472.63170@v 46g2000cwv.goog legroups.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:[color=blue]
> 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_s taging dls
> where dls.key2_value = f.pickno)
> and not exists (select * from
> document_link_s torage dls
> where dls.key2_value = f.pickno)
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <KyussWren@gmai l.com> wrote in message
> news:1146621327 .262902.130050@ v46g2000cwv.goo glegroups.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.k ey2_value
> document_link_s taging.key2_val ue
> document_link_s torage.key2_val ue
>
> And these three fields are populated from a substring of the filenames
> which are generated in another table.
>
> Filenametbl.pic kno
>
> 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_s taging) and pickno not in (select key2_value from
> document_link_s torage)
>
> 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_s taging)
>
> returns 200 records in this scenario,
>
> this query returns 0:
> select * from Filenametbl where pickno not in (select key2_value from
> document_link_s torage)
>
> 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.[/color]

An odd quandry.

An odd quandry.

Comment

Comment

Comment

Comment