August 20, 2009 at 7:31 pm
Hi,
I have a doubt. This is my table.
create table t1
(id int)
insert into t1
select 1
union all
select 2
union all
select 3
select * from t1 where id = 675
Here output is nothing.
Question is in waht cases i can see NULL as output.
When does sql server shows nothing and when does sql server shows me NULL if i use the WHERE clause?
if am saying, select @@ROWCOUNT then am getting output as 0.
Why so?
August 20, 2009 at 7:45 pm
NULL is the absence of a value in a column for a given row (or a variable), not the absence of a row.
@@ROWCOUNT is the count of rows returned or affected by your last query. If no rows meet your WHERE conditions, then no rows are returned and @@rowcount will be equal to zero. Zero is a value, just like 1, -1, or 99999999.
In the example below, @name will not change, because no row is found to supply a new value. It is NOT set to NULL.
-- show that no rows will be returned
select * from sys.databases
where 1 = 0
-- see if the query changes the value of @name
declare @name varchar(50)
set @name = 'XXXX'
select @name = name from sys.databases
where 1 = 0
select @name
However, when a row is returned, but there is no value for a column, you will see SQL return a NULL.
Compare the JOIN and LEFT JOIN results in the two queries below, then let me know if you have any other specific questions.
--
declare @a table (id int, name varchar(5))
declare @b-2 table (id int, EligibilityDate datetime)
insert into @a
select 1, 'Peter' union all
select 2, 'Paul' union all
select 3, 'Mary'
insert into @b-2
-- there is no "2", because Paul is not yet eligible
select 1, '1/1/2009' union all
select 3, '2/2/2009'
-- this query returns only those rows that match
select a.id, a.name, b.EligibilityDate
from @a a
join @b-2 b on b.id = a.id
-- this query returns all rows for @a, values for @b-2 columns when found,
-- and NULLs when a matching @b-2 row is not found
select a.id, a.name, b.EligibilityDate
from @a a
LEFT join @b-2 b on b.id = a.id
--
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 8:04 pm
Bob Hovious (8/20/2009)
NULL is the absence of a value in a column for a given row (or a variable), not the absence of a row.If no rows meet your WHERE condition, then no rows are returned and @@rowcount will be equal to zero.
In the example below, @name will not change, because no row is found to supply a new value. It is NOT set to NULL.
-- show that no rows will be returned
select * from sys.databases
where 1 = 0
-- see if the query changes the value of @name
declare @name varchar(50)
set @name = 'XXXX'
select @name = name from sys.databases
where 1 = 0
select @name
Well - there is this thing called a Null Set that is returned from a query where no rows match the predicate. In the above example, the query does not modify the variable because the query does not return any rows that would be able to set the variable.
Change it to:
Set @name = (Select Name From sys.databases Where 1 = 0);
And now we have set the @name variable to the result of the query, which is the null set - and we now have NULL as the value.
So, depending upon how you implement the code can have different results. To validate the above, set the @name variable to some value before calling it, as in:
Set @name = 'Before';
Set @name = (Select Name From sys.databases Where 1 = 0);
Back to the OP's question - can you clarify what you mean by Null and Nothing? When you run a query that does not return any rows, the null set is returned and @@rowcount = 0. What the client tool does with that depends upon how the client tool was designed. SSMS recognizes when the null set is returned and does not show anything in the results pane, but other tools could show something different.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2009 at 8:12 pm
Great stuff!
August 20, 2009 at 8:19 pm
Hey Jeffery, thanks for joining in. 🙂
I will confess to totally overlooking the null set as you illustrated. So let me ask you this question:
Why does your version return the null set and my version doesn't?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 8:31 pm
Bob Hovious (8/20/2009)
Hey Jeffery, thanks for joining in. 🙂I will confess to totally overlooking the null set as you illustrated. So let me ask you this question:
Why does your version return the null set and my version doesn't?
My version returns the null set because I am explicitly setting a variable. The set statement has to do something - so it is going to set the variable to the value returned from the select. Since the select statement has no rows that satisfy the query, the only thing that can be returned is the null set.
Yours will not return the null set - because you are not setting the variable to the results of the select statement. Yours is setting the variable to a value in a column - and will only set the variable when there is at least one row that satisfies the query.
Think about what happens in your version if more than 1 row satisfies the query? In my version - I get a run-time error, in yours you will get a value set - just don't know which one unless you have defined an order by.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2009 at 10:06 pm
Got it. I was looking at the difference in execution plans, and you example has a compute scalar... which obviously calculates a single value. I rarely set a variable the way you did with the subquery, and haven't thought about the mechanics under the hood before. Thanks for the education.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 20, 2009 at 10:14 pm
Bob Hovious (8/20/2009)
Got it. I was looking at the difference in execution plans, and you example has a compute scalar... which obviously calculates a single value. I rarely set a variable the way you did with the subquery, and haven't thought about the mechanics under the hood before. Thanks for the education.
No problem - I usually use the SET form to set variables because it does give me an error at run-time if the query returns more than a single row. I'll use the SELECT form when I want to set more than one variable from the same query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2009 at 10:19 pm
For what it's worth, I saw no difference in the execution plan for your example whether or not a SET or a SELECT is used. Apparently it is the subquery to display a single column that results in the scalar computation. I almost always use the SELECT @var1 = col1 FROM SomeTbl form, because I seldom seem to need just one value out of a table.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply