What is up with this?!!! (for real this time)

[mark]

I am trying to get a list of modules that do not have associated submodules.
I don't think I'm doing anything wrong. Could the index table be screwed
up or am I just stoopid or both?

select * from modules where moduleid = 892 : returns 1 record (true)

select * from submodules where moduleid = 892 " returns 0 records (true)

select * from modules where moduleid not in (select moduleid from submodules)
: returns 0 records... what the ?!

select moduleid from modules where not exists (select moduleid from submodules)
: returns 0 records...

Whiskey Tango Foxtrot? ... Over.


Mark

[Rune Bivrin]

Answers inline.

"mark" <mtaylor@globalsolar.com> wrote in news:3d499978$1@10.1.10.29:

> select * from modules where moduleid not in (select moduleid from
> submodules)
>: returns 0 records... what the ?!

Don't quite know about this one. Have you tried reversing the condition
[where moduleid IN (select ...] to see if that returns all rows from
modules? What is the data type of the columns in the two tables. Are they
different?


> select moduleid from modules where not exists (select moduleid from
> submodules)
>: returns 0 records...

As written, it will return no rows from modules unless submodules is empty,
since there's no correlation between query and sub-query

I think that should read:

select * from modules where not exists (select * from
submodules where moduleid = modules.moduleid)

Rune Bivrin

[mark]

Rune, thanks for the research. I also came up with:

select * from modules m
left join submodules sm on sm.moduleid = m.moduleid
where sm.moduleid is null

901 records returned

which yields the same result as your query:

select * from modules where not exists (select * from
submodules where moduleid = modules.moduleid)

901 records returned

Cool. Your query is probably better as long as moduleid is indexed on the
submodule record. But I wonder if SQL Server still takes the same route.
In theory it does.

Mark





Rune Bivrin <rune@bivrin.com> wrote:
>Answers inline.
>
>"mark" <mtaylor@globalsolar.com> wrote in news:3d499978$1@10.1.10.29:
>
>> select * from modules where moduleid not in (select moduleid from
>> submodules)
>>: returns 0 records... what the ?!
>
>Don't quite know about this one. Have you tried reversing the condition

>[where moduleid IN (select ...] to see if that returns all rows from
>modules? What is the data type of the columns in the two tables. Are they

>different?
>
>
>> select moduleid from modules where not exists (select moduleid from
>> submodules)
>>: returns 0 records...
>
>As written, it will return no rows from modules unless submodules is empty,

>since there's no correlation between query and sub-query
>
>I think that should read:
>
>select * from modules where not exists (select * from
> submodules where moduleid = modules.moduleid)
>
>Rune Bivrin
>

[mark]

Theory about how SQL Server optimizes for the most efficient retrieval aside,
your query uses ~ 50% less resources than my query. I ran both queries in
a batch and displayed the execution plans. Interesting.

Mark



Rune Bivrin <rune@bivrin.com> wrote:
>Answers inline.
>
>"mark" <mtaylor@globalsolar.com> wrote in news:3d499978$1@10.1.10.29:
>
>> select * from modules where moduleid not in (select moduleid from
>> submodules)
>>: returns 0 records... what the ?!
>
>Don't quite know about this one. Have you tried reversing the condition

>[where moduleid IN (select ...] to see if that returns all rows from
>modules? What is the data type of the columns in the two tables. Are they

>different?
>
>
>> select moduleid from modules where not exists (select moduleid from
>> submodules)
>>: returns 0 records...
>
>As written, it will return no rows from modules unless submodules is empty,

>since there's no correlation between query and sub-query
>
>I think that should read:
>
>select * from modules where not exists (select * from
> submodules where moduleid = modules.moduleid)
>
>Rune Bivrin
>