Subquery Question

23 Sep 2005 3:09 PM

Mike Labosh

SELECT DISTINCT ssa.CMRKey, ssa.ContactphoneNumber
FROM SampleSourceArchive ssa
WHERE SampleSourceKey = @sampleSourceKey

AND CMRKey IS NOT NULL -- is this line not needed

AND ssa.ContactPhoneNumber > ''
AND NOT EXISTS (
   SELECT CMRKey, PhoneNumber
   FROM CMRPhone

   WHERE CMRKey = ssa.CMRKey -- because of this line?

    AND PhoneNumber = ssa.ContactphoneNumber
)

I think the answer is yes because a NULL in one table should not ever equal
a NULL in another table (or columns in the same table for that matter)
Correct?

--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

23 Sep 2005 3:17 PM

SQL

Depends on your ansi null settings
try this

declare @1 int,@2 int
if @1 = @2
select 'yes'
else
select 'no'

play with SET ANSI_NULLS ON/OFF

http://sqlservercode.blogspot.com/

Show quote

"Mike Labosh" wrote:

> SELECT DISTINCT ssa.CMRKey, ssa.ContactphoneNumber
> FROM SampleSourceArchive ssa
> WHERE SampleSourceKey = @sampleSourceKey
>
>   AND CMRKey IS NOT NULL -- is this line not needed
>
>   AND ssa.ContactPhoneNumber > ''
>   AND NOT EXISTS (
>    SELECT CMRKey, PhoneNumber
>    FROM CMRPhone
>
>    WHERE CMRKey = ssa.CMRKey -- because of this line?
>
>     AND PhoneNumber = ssa.ContactphoneNumber
> )
>
> I think the answer is yes because a NULL in one table should not ever equal
> a NULL in another table (or columns in the same table for that matter)
> Correct?
>
> --
> Peace & happy computing,
>
> Mike Labosh, MCSD
>
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>
>
>

23 Sep 2005 3:30 PM

Mike Labosh

> declare @1 int,@2 int
> if @1 = @2
> select 'yes'
> else
> select 'no'

It said 'no' That's perfect. I will go look at ANSI_NULLS in BOL too.

--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave Mustane

23 Sep 2005 3:20 PM

Hari

Hi Mike,

In my opinion you're in doing so, it will also make this query
optimized.

-Hari

23 Sep 2005 3:20 PM

Hari

Hi Mike,

In my opinion you're in doing so, it will also make this query
optimized.

-Hari Sharma

23 Sep 2005 3:30 PM

Raymond D'Anjou

No it's not neededand I've never had to use this filter in a join.
Well there may be an issue with ANSI_NULL but I've always left ANSI_NULL to
the default value OFF.
Run this in QA:
select case WHEN NULL = NULL THEN 'TRUE' WHEN NULL <> NULL THEN 'FALSE' ELSE
'UNKNOWN' END

Show quote

"Mike Labosh" <mlab***@hotmail.com> wrote in message
news:OQT4yCFwFHA.3236@TK2MSFTNGP14.phx.gbl...
> SELECT DISTINCT ssa.CMRKey, ssa.ContactphoneNumber
> FROM SampleSourceArchive ssa
> WHERE SampleSourceKey = @sampleSourceKey
>
> AND CMRKey IS NOT NULL -- is this line not needed
>
> AND ssa.ContactPhoneNumber > ''
> AND NOT EXISTS (
>   SELECT CMRKey, PhoneNumber
>   FROM CMRPhone
>
>   WHERE CMRKey = ssa.CMRKey -- because of this line?
>
>    AND PhoneNumber = ssa.ContactphoneNumber
> )
>
> I think the answer is yes because a NULL in one table should not ever
> equal a NULL in another table (or columns in the same table for that
> matter) Correct?
>
> --
> Peace & happy computing,
>
> Mike Labosh, MCSD
>
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>

23 Sep 2005 5:59 PM

--CELKO--

>> I've always left ANSI_NULL to the default value OFF. <<

Why? Do you spit on other ANSI/ISO Standards, too? This is a bitch
to maintain, to port or even to read if you know SQL.

23 Sep 2005 6:29 PM

Raymond D'Anjou

Sorry, I meant ON.
Gee, I just got Celkoed.
What a way to finish the week. :-(

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1127498357.401322.153550@f14g2000cwb.googlegroups.com...
>>> I've always left ANSI_NULL to the default value OFF. <<
>
> Why? Do you spit on other ANSI/ISO Standards, too? This is a bitch
> to maintain, to port or even to read if you know SQL.
>

23 Sep 2005 7:22 PM

Mike Labosh

>> Why? Do you spit on other ANSI/ISO Standards, too? This is a bitch
>> to maintain, to port or even to read if you know SQL.

> Sorry, I meant ON.
> Gee, I just got Celkoed.
> What a way to finish the week. :-(

OK, so I'll step up and spit on them. If I use quirky Microsoft-only
techniques, I make it too prohibitive for them to take away my SQL Server
and make me learn Oracle or PostGres or MySql or DB2...

INSERT INTO Celko VALUES ('Evil Trolling Grin')

--
Peace & happy computing,

Mike Labosh, MCSD

"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave Mustane

23 Sep 2005 8:59 PM

Hugo Kornelis

On Fri, 23 Sep 2005 11:09:02 -0400, Mike Labosh wrote:

Show quote

> SELECT DISTINCT ssa.CMRKey, ssa.ContactphoneNumber
> FROM SampleSourceArchive ssa
> WHERE SampleSourceKey = @sampleSourceKey
>
> AND CMRKey IS NOT NULL -- is this line not needed
>
> AND ssa.ContactPhoneNumber > ''
> AND NOT EXISTS (
>   SELECT CMRKey, PhoneNumber
>   FROM CMRPhone
>
>   WHERE CMRKey = ssa.CMRKey -- because of this line?
>
>    AND PhoneNumber = ssa.ContactphoneNumber
> )
>
>I think the answer is yes because a NULL in one table should not ever equal
>a NULL in another table (or columns in the same table for that matter)
>Correct?

Hi Mike,

Assuming proper ANSI NULL settings, you could remove the line. But I'd
leave it in all the same, for two reasons:
1. Makes the query easier to understand for the readers;
2. Gives the optimizer an extra possibility to save the engine some
work, either by checking the NULL condition before performing the
subquery, or maybe even by doing an index scan on an index that holds
CMRKey.

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)