|
Home > Archive > MS SQL Server New Users > November 2005 > QUERY ANALYSER MYSTERY
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
QUERY ANALYSER MYSTERY
|
|
|
| Hi,
If anyone can explain why this is happening, it would be greatly appreciated:
Problem Description:
1. Within SQL SERVER ENTERPRISE QUERY ANALYSER, an SQL statement is entered
and executed. No results are returned.
SELECT ID FROM PRODUCT_TABLE WHERE
ID NOT IN (SELECT ID FROM HISTORY_TABLE)
2. The same SQL is setup in a STORED PROCEDURE and executed from the QUERY ANALYSER and the correct results are now returned.
QUESTION:
Why is the SQL in the STORED PROCEDURE working perfectly while the exact same SQL as entered into the QUERY ANALYSER, does not???
The tables have the following number of rows:
HISTORY_TABLE has 650,000 rows
PRODUCT_TABLE has 6,000 rows
Thanks to all who respond.
| |
| Adam Machanic 2005-11-20, 1:23 pm |
| What does "no results are returned" mean? Do you mean that literally nothing happens, or that the query returns an empty rowset?
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"James" < james_ausminski@hotm
ail.com> wrote in message news:3m2gf.47379$Z8.29571@read2.cgocable.net...
Hi,
If anyone can explain why this is happening, it would be greatly appreciated:
Problem Description:
1. Within SQL SERVER ENTERPRISE QUERY ANALYSER, an SQL statement is entered
and executed. No results are returned.
SELECT ID FROM PRODUCT_TABLE WHERE
ID NOT IN (SELECT ID FROM HISTORY_TABLE)
2. The same SQL is setup in a STORED PROCEDURE and executed from the QUERY ANALYSER and the correct results are now returned.
QUESTION:
Why is the SQL in the STORED PROCEDURE working perfectly while the exact same SQL as entered into the QUERY ANALYSER, does not???
The tables have the following number of rows:
HISTORY_TABLE has 650,000 rows
PRODUCT_TABLE has 6,000 rows
Thanks to all who respond.
| |
| Mike Epprecht \(SQL MVP\) 2005-11-20, 1:23 pm |
| Hi
When doing sub queries, always fully qualify the tables and columns. A small
mistake can occur and you never notice it.
If ID does not exist in the HISTORY_TABLE, it would still return valid rows
as it uses PRODUCT_TABLE's ID.
SELECT PRODUCT_TABLE.ID FROM PRODUCT_TABLE WHERE
PRODUCT_TABLE.ID NOT IN (SELECT HISTORY_TABLE.ID FROM HISTORY_TABLE)
Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"James" < james_ausminski@hotm
ail.com> wrote in message
news:3m2gf.47379$Z8.29571@read2.cgocable.net...
Hi,
If anyone can explain why this is happening, it would be greatly
appreciated:
Problem Description:
1. Within SQL SERVER ENTERPRISE QUERY ANALYSER, an SQL statement is entered
and executed. No results are returned.
SELECT ID FROM PRODUCT_TABLE WHERE
ID NOT IN (SELECT ID FROM HISTORY_TABLE)
2. The same SQL is setup in a STORED PROCEDURE and executed from the QUERY
ANALYSER and the correct results are now returned.
QUESTION:
Why is the SQL in the STORED PROCEDURE working perfectly while the exact
same SQL as entered into the QUERY ANALYSER, does not???
The tables have the following number of rows:
HISTORY_TABLE has 650,000 rows
PRODUCT_TABLE has 6,000 rows
Thanks to all who respond.
| |
|
| It returns an empty Row Set. Sorry, I should have clarified.
Thanks
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:udC16jf7FHA.1864@TK2MSFTNGP12.phx.gbl...
What does "no results are returned" mean? Do you mean that literally nothing happens, or that the query returns an empty rowset?
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"James" < james_ausminski@hotm
ail.com> wrote in message news:3m2gf.47379$Z8.29571@read2.cgocable.net...
Hi,
If anyone can explain why this is happening, it would be greatly appreciated:
Problem Description:
1. Within SQL SERVER ENTERPRISE QUERY ANALYSER, an SQL statement is entered
and executed. No results are returned.
SELECT ID FROM PRODUCT_TABLE WHERE
ID NOT IN (SELECT ID FROM HISTORY_TABLE)
2. The same SQL is setup in a STORED PROCEDURE and executed from the QUERY ANALYSER and the correct results are now returned.
QUESTION:
Why is the SQL in the STORED PROCEDURE working perfectly while the exact same SQL as entered into the QUERY ANALYSER, does not???
The tables have the following number of rows:
HISTORY_TABLE has 650,000 rows
PRODUCT_TABLE has 6,000 rows
Thanks to all who respond.
| |
|
| Hi Mike,
Yes, I had previously tried fully qualifying the field ID to make sure.
I've carefully checked everything over and over again.
The exact same SQL works perfectly when implemented through a STORED
PROCEDURE and returns the exact rows it should return. However, when I
enter that same SQL directly into the QUERY ANALYSER pane and execute, it
returns an empty row set.
Thanks for any feedback you can offer.
"Mike Epprecht (SQL MVP)" <mike@epprecht.net> wrote in message
news:eOI$kwf7FHA.4036@TK2MSFTNGP11.phx.gbl...
> Hi
>
> When doing sub queries, always fully qualify the tables and columns. A
> small mistake can occur and you never notice it.
> If ID does not exist in the HISTORY_TABLE, it would still return valid
> rows as it uses PRODUCT_TABLE's ID.
>
> SELECT PRODUCT_TABLE.ID FROM PRODUCT_TABLE WHERE
> PRODUCT_TABLE.ID NOT IN (SELECT HISTORY_TABLE.ID FROM HISTORY_TABLE)
>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: mike@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "James" < james_ausminski@hotm
ail.com> wrote in message
> news:3m2gf.47379$Z8.29571@read2.cgocable.net...
> Hi,
>
> If anyone can explain why this is happening, it would be greatly
> appreciated:
>
> Problem Description:
>
> 1. Within SQL SERVER ENTERPRISE QUERY ANALYSER, an SQL statement is
> entered
> and executed. No results are returned.
>
> SELECT ID FROM PRODUCT_TABLE WHERE
> ID NOT IN (SELECT ID FROM HISTORY_TABLE)
>
> 2. The same SQL is setup in a STORED PROCEDURE and executed from the QUERY
> ANALYSER and the correct results are now returned.
>
> QUESTION:
> Why is the SQL in the STORED PROCEDURE working perfectly while the exact
> same SQL as entered into the QUERY ANALYSER, does not???
>
> The tables have the following number of rows:
> HISTORY_TABLE has 650,000 rows
> PRODUCT_TABLE has 6,000 rows
>
> Thanks to all who respond.
>
>
>
>
>
>
| |
| Andrew J. Kelly 2005-11-20, 8:23 pm |
| In addition to what Mike stated about aliasing your objects you should also use [ ] around object names that are reserved words such as ID. One thing I would check is to see what the SET settings are like and if they are different between the sp and the QA session. Stored procedures, tables etc. have several SET settings embedded in them when you create the objects. If the settings are different in the QA session you may get different results. Have a look at these sections in BooksOnLine and any associated entries as well:
SET, options that affect results
SET, miscellaneous
--
Andrew J. Kelly SQL MVP
"James" < james_ausminski@hotm
ail.com> wrote in message news:3m2gf.47379$Z8.29571@read2.cgocable.net...
Hi,
If anyone can explain why this is happening, it would be greatly appreciated:
Problem Description:
1. Within SQL SERVER ENTERPRISE QUERY ANALYSER, an SQL statement is entered
and executed. No results are returned.
SELECT ID FROM PRODUCT_TABLE WHERE
ID NOT IN (SELECT ID FROM HISTORY_TABLE)
2. The same SQL is setup in a STORED PROCEDURE and executed from the QUERY ANALYSER and the correct results are now returned.
QUESTION:
Why is the SQL in the STORED PROCEDURE working perfectly while the exact same SQL as entered into the QUERY ANALYSER, does not???
The tables have the following number of rows:
HISTORY_TABLE has 650,000 rows
PRODUCT_TABLE has 6,000 rows
Thanks to all who respond.
| |
| Hugo Kornelis 2005-11-20, 8:23 pm |
| On Sun, 20 Nov 2005 12:27:29 -0500, James wrote:
>Hi,
>
>If anyone can explain why this is happening, it would be greatly appreciated:
>
>Problem Description:
>
>1. Within SQL SERVER ENTERPRISE QUERY ANALYSER, an SQL statement is entered
>and executed. No results are returned.
>
>SELECT ID FROM PRODUCT_TABLE WHERE
>ID NOT IN (SELECT ID FROM HISTORY_TABLE)
>
>2. The same SQL is setup in a STORED PROCEDURE and executed from the QUERY ANALYSER and the correct results are now returned.
>
>QUESTION:
>Why is the SQL in the STORED PROCEDURE working perfectly while the exact same SQL as entered into the QUERY ANALYSER, does not???
>
>The tables have the following number of rows:
> HISTORY_TABLE has 650,000 rows
> PRODUCT_TABLE has 6,000 rows
>
>Thanks to all who respond.
Hi James,
Andrew's message triggered the answer in my head. I'm quite sure that it
is caused by different SET settings. Specifically the SET ANSI_NULLS
option.
With ANSI standard behaviour, NULL is never equal to anything, but also
never unequal to anything. Now, what happens if the subquery returns one
of more rows with a NULL value for ID? In order for the NOT IN to be
true, the ID from Product_Table must be unequal to the ID in all rows in
the subquery. But since the subquery has a NULL and nothing is never
unequal to NULL, the NOT IN can never evaluate to true for any value of
ID in Product_table. Hence, no rows will be returned.
With SET ANSI_NULLS OFF (a setting you should only use to keep old code
that relies on this running until you fix it), a NULL value is
considered equal to another NULL value and unequal to anything else.
That "solves" the problem of the NOT IN not returning any rows.
I wrote solve in quotation marks, because it's a bad solution. You
should use ANSI standard settings wherever possible, because that's what
most people expect. Here are two solutions to get around the "problem"
caused by ID in History_table being NULL:
1. (Best)
SELECT ID
FROM Product_Table
WHERE NOT EXISTS
(SELECT *
FROM History_Table
WHERE History_Table.ID = Product_Table.ID)
2. (Least change from current)
SELECT ID
FROM Product_Table
WHERE ID NOT IN
(SELECT ID
FROM History_Table
WHERE ID IS NOT NULL)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
| |
|
| The feedback you and everyone else who responded to this issue, is much
appreciated.
Thanks very much,
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:j2u1o11r1hhngbj
rnp9862hponlv465r5s@
4ax.com...
> On Sun, 20 Nov 2005 12:27:29 -0500, James wrote:
>
>
> Hi James,
>
> Andrew's message triggered the answer in my head. I'm quite sure that it
> is caused by different SET settings. Specifically the SET ANSI_NULLS
> option.
>
> With ANSI standard behaviour, NULL is never equal to anything, but also
> never unequal to anything. Now, what happens if the subquery returns one
> of more rows with a NULL value for ID? In order for the NOT IN to be
> true, the ID from Product_Table must be unequal to the ID in all rows in
> the subquery. But since the subquery has a NULL and nothing is never
> unequal to NULL, the NOT IN can never evaluate to true for any value of
> ID in Product_table. Hence, no rows will be returned.
>
> With SET ANSI_NULLS OFF (a setting you should only use to keep old code
> that relies on this running until you fix it), a NULL value is
> considered equal to another NULL value and unequal to anything else.
> That "solves" the problem of the NOT IN not returning any rows.
>
> I wrote solve in quotation marks, because it's a bad solution. You
> should use ANSI standard settings wherever possible, because that's what
> most people expect. Here are two solutions to get around the "problem"
> caused by ID in History_table being NULL:
>
> 1. (Best)
>
> SELECT ID
> FROM Product_Table
> WHERE NOT EXISTS
> (SELECT *
> FROM History_Table
> WHERE History_Table.ID = Product_Table.ID)
>
> 2. (Least change from current)
>
> SELECT ID
> FROM Product_Table
> WHERE ID NOT IN
> (SELECT ID
> FROM History_Table
> WHERE ID IS NOT NULL)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|
|
|
|
|