|
What is the difference between IS NULL, = 'NULL' in MS SQL. When I query a table to get the first 10 rows with the following condition:
SELECT TOP 10 *
FROM TableA
WHERE ColumnX IS NULL
It gives me random rows, not the first 10 rows. ColumnX is all nulls but I need the first n rows. I noticed when I use this condition then I always get the first 10 rows:
WHERE ColumnX = 'NULL'
I know IsNull is a function but what is the difference between the other two?
|
|
|
|
|
= 'NULL' always evaluates to true...
|
|
|
|
|
Do you really mean = 'NULL' (in quotation marks)? If so, 'NULL' is just a string whereas would be 'ABC' so it doesn't have anything to do with the reserver word NULL in SQL.
If you mean = NULL (without quotation marks), that condition based on ANSI standard is always false (nothing is equal to NULL.
In different databases this may vary and in SQL Server the logic can be modified in database settings.
The need to optimize rises from a bad design
|
|
|
|
|
Mika Wendelius wrote: If you mean = NULL (without quotation marks), that condition based on ANSI standard is always false (nothing is equal to NULL.
Actually, they can evaluate to (unknown)
check this article[^]
|
|
|
|
|
Couldn't open the article, but that's exactly my point.
NULL is never equal to anything since it means unknown. For example NULL = NULL is not true since we don't know the values that are compared. Same goes with != (or <> ) operators etc.
The need to optimize rises from a bad design
|
|
|
|
|
use is null like you would use ...== null in c# ok? dont place "=" near null.
isnull() you use if you can get a null value in a column (or whatever) and you place a 2nd value (of same type) there if null happens.
declare @a int
set @a = 15
if (@a is not null)
begin
SELECT id,name,age,isnull(adress,'no adress') FROM dbo.Clients
end
got it?!
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
CodingYoshi wrote: SELECT TOP 10 *
FROM TableA
WHERE ColumnX IS NULL
This will give you random records where ColumnX is null.
A top qualifier really needs an Order By as well
CodingYoshi wrote: WHERE ColumnX = 'NULL'
This is testing for the string value of the characters "NULL"
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
When run the following script returns an ORA-00936 missing expression error.
[Code]
select MemNumber,
Turnover
from
(
select mem_number MemNumber,
sum(trans_turnover) Turnover
from members
join transact
on mem_number = trans_code
where Trans_date between '&Start' and '&End'
and mem_barred = 0
group by mem_number
order by turnover desc
)
where rownum <=200
/
[/code]
Please tell me how I should fix it.
Regards,
Alf Stockton
|
|
|
|
|
[Code]
where rownum <=200
[/code]
Your where clause references a "rownum" column...
Your select statement does not produce a column named "rownum"...
hth
Al
|
|
|
|
|
Thank you. I will try your suggestion.
Regards,
Alf Stockton
|
|
|
|
|
rownum is a pseudocolumn so it's created automatically and defining it in the inner select would change the logic of the statement.
The need to optimize rises from a bad design
|
|
|
|
|
|
Didn't quite understand your point? There's a good example in the article:
select *
from
(select *
from t
order by id)
where rownum <= 10
which returns top 10 rows. Rownum is placed on the outer query and the inner query selects only columns from the table t. Rownum is used on the ordered result set to ensure that really top 10 rows based on id column are returned.
I understood that the original query in this question was doing top N fetch.
The need to optimize rises from a bad design
|
|
|
|
|
I know that's why I said good catch... when I saw the rownum I was thinking of it as
(
SELECT col1, col2,ROW_NUMBER() OVER (PARTITION BY col1_id ORDER BY col1) AS rownum
FROM employee
)
where rownum < 200
My bad..
hth
Al
|
|
|
|
|
Oh, I see.
I clearly misunderstood your reply to my post. Sorry for that.
The need to optimize rises from a bad design
|
|
|
|
|
If you're running this in SQL*Plus or a program based on that, I would guess that the problem is in & character. This is normally reserved for prompting values so your statement may evaluate to
where Trans_date between '' and ''
Also you 'should' provide an alias for sub select in FROM.
If you're using tools mentioned above, they should show you the line where the problem is.
The need to optimize rises from a bad design
|
|
|
|
|
Please can someone help me!!! I use SQL Server 2005 and I need to import the data in a sample SQL Db into my own SQL database.... I dont know how to do this please help. my boss is on my neck
|
|
|
|
|
Check this google result.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
|
Two books that I've found useful. They are bit old (from the time before electricity  ), but excellent ones:
- An introduction to database systems / C.J. Date
- Fundamentals of database systems / Elmasri & Navathe
The need to optimize rises from a bad design
|
|
|
|
|
I'll second both of those suggested books
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
Thanx Mika
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
No problem
The need to optimize rises from a bad design
|
|
|
|
|
If you are using Sql Server then you should look at Pro Sql Server 200x Database Design and Optimization by Louis Davidson, et al. (Apress).
|
|
|
|
|
download absolute beginners lesson in microsoft visual studio site.
You have there video about c# and sql server.
BOB TUBOR lessons.
Later on you will have something to question, not now 
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
