Question about NULL

Question

Question about NULL

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
September 20, 2007 at 11:00 am

#178481

Hi,
I'd like to query a table excluding all records with a specific value in a column, this column has NULL in it. so when i run the query
select * from tbl1
where col1 <> 'value'
It displays all records where col1 has a value other than 'value' but also leaves out the NULLS I know I can also add
or col1 is null
is there a better way to do this or a proper way this should be done?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

You can use either the OR statement or use ISNULL() or COALESCE(). I like ISNULL for readability, personally. However my understanding is that it can cause stored procedure plan recompilation if not used properly.

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 2

Ok thank you for your help, I'll look into incorporating your suggestion.

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 3

Hi Aaron Ingold ,

That is ok that we can use ISNULL to solve this problem BUT How we can make use of COLESAC function here to solve this problem???

Can u please explain and give example for using COLESAC function in this situation???

Thanks,
Prashant

Prashant Pandey SSChasing Mays Points: 657 More actions · Answer 4

Hi Marcus Farrugia,

I think u need to do some modification in the where claues otherwise i d't think it is possible to get the NUll and also the record of that specified value. U can make use of IN in this situtation like below:

Select * From Test2 Where Name IN ('prashant',NULL)

Thanks,
Prashant

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Using Null in the IN clause will only work if you have ANSI_NULLS set to OFF. If ANSI_NULLS is on you need to use Is Null or Is Not Null in order to determine the status of NULL values.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Prashant,

I believe the ISNULL function is a T-SQL addition to ANSI SQL and COALESCE is an ANSI SQL function. The COALESCE function takes a comma seperated list of values and returns the first Non-Null value while the ISNULL only takes 2 values. For example:

Select * from test where COALESCE(first_name, last_name, middle_name) is not null - will return any row where 1 of the name columns contains a non-null value in order to do the same thing with IsNull you would need multiple criteria in the where like:

Select * From test where IsNull(first_name, last_name) is not null or middle_name is not null.

Hope this helps.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

You could also issue a SET ANSI_NULLS OFF at the beginning of your query which would allow Null values to be evaluated using = and <>.

If you are only dealing with a single column evaluation like in your example I would add the Or col1 is null to the where clause because, as was mentioned, using a function on a column in a where clause can cause re-compilation and force table scans.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question