Getting Nulls from a select/where statement

I am trying to get the nulls from my columns and when I do a select/where
statement I do not get anything.
I have used Null, NULL, IS NULL, IS NOT NULL.
What is the right thing to use?
I am using SQL 2005 Enterprise.
Thank you
Dee
Please post the DDL of the tables involved, as well as your query.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Dee" <Dee@.discussions.microsoft.com> wrote in message
news:92A38A34-1F8B-42C1-9973-B67EA29ECAA7@.microsoft.com...
I am trying to get the nulls from my columns and when I do a select/where
statement I do not get anything.
I have used Null, NULL, IS NULL, IS NOT NULL.
What is the right thing to use?
I am using SQL 2005 Enterprise.
Thank you
Dee
|||"Dee" <Dee@.discussions.microsoft.com> wrote in message
news:92A38A34-1F8B-42C1-9973-B67EA29ECAA7@.microsoft.com...
>I am trying to get the nulls from my columns and when I do a select/where
> statement I do not get anything.
> I have used Null, NULL, IS NULL, IS NOT NULL.
> What is the right thing to use?
> I am using SQL 2005 Enterprise.
> Thank you
> Dee
Try this example:
CREATE TABLE tbl (x INT NOT NULL PRIMARY KEY, z INT NULL);
INSERT INTO tbl (x,z) VALUES (1,1);
INSERT INTO tbl (x,z) VALUES (2,NULL);
SELECT x,z FROM tbl WHERE z IS NULL;
SELECT x,z FROM tbl WHERE z IS NOT NULL;
If that doesn't answer your question then please post some code to reproduce
the problem (CREATE, INSERT and SELECT like I have done).
David Portas
|||I am at home now and do not have the information you need. I can create it
later, but let me explain. I created a table from our store xml file, some
of the columns have nulls in them. What I am wanting to do is do a report of
the product id and any columns that match that id that has null values.
Thanks
Dee
"David Portas" wrote:

> "Dee" <Dee@.discussions.microsoft.com> wrote in message
> news:92A38A34-1F8B-42C1-9973-B67EA29ECAA7@.microsoft.com...
> Try this example:
> CREATE TABLE tbl (x INT NOT NULL PRIMARY KEY, z INT NULL);
> INSERT INTO tbl (x,z) VALUES (1,1);
> INSERT INTO tbl (x,z) VALUES (2,NULL);
> SELECT x,z FROM tbl WHERE z IS NULL;
> SELECT x,z FROM tbl WHERE z IS NOT NULL;
>
> If that doesn't answer your question then please post some code to reproduce
> the problem (CREATE, INSERT and SELECT like I have done).
> --
> David Portas
>
>
|||Here is what I think you mean by the DDT:
USE [yahoostore]
GO
/****** Object: Table [dbo].[PRODUCTS] Script Date: 02/06/2008 08:20:27
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PRODUCTS](
[ProductID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Code] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ProductDesc] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Url] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Orderable] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Taxable] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HTMLPath] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Caption] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Thumb] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Picture] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BasePrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocalizedBasePrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[OriginalPrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocalizedOriginalPrice] [varchar](255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[SalePrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LocalizedSalePrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[Availability] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Weight] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OptionLists] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Code I am using:
SELECT [ProductID]
,[Code]
,[ProductDesc]
,[Url]
,[Orderable]
,[Taxable]
,[HTMLPath]
,[Caption]
,[Thumb]
,[Picture]
,[BasePrice]
,[LocalizedBasePrice]
,[OriginalPrice]
,[LocalizedOriginalPrice]
,[SalePrice]
,[LocalizedSalePrice]
,[Availability]
,[Weight]
,[OptionLists]
FROM [yahoostore].[dbo].[PRODUCTS]
where Url = 'NULL'
Thank you
Dee
"Dee" wrote:
[vbcol=seagreen]
> I am at home now and do not have the information you need. I can create it
> later, but let me explain. I created a table from our store xml file, some
> of the columns have nulls in them. What I am wanting to do is do a report of
> the product id and any columns that match that id that has null values.
> Thanks
> Dee
> "David Portas" wrote:
|||here you are comparing with the string "NULL"
FROM [yahoostore].[dbo].[PRODUCTS]
where Url = 'NULL'
if you want to find null,
where Url is NULL
Thanks, Liliya
"Dee" wrote:
[vbcol=seagreen]
> Here is what I think you mean by the DDT:
> USE [yahoostore]
> GO
> /****** Object: Table [dbo].[PRODUCTS] Script Date: 02/06/2008 08:20:27
> ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_PADDING ON
> GO
> CREATE TABLE [dbo].[PRODUCTS](
> [ProductID] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Code] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [ProductDesc] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Url] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Orderable] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Taxable] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [HTMLPath] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Caption] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Thumb] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Picture] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [BasePrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [LocalizedBasePrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL,
> [OriginalPrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [LocalizedOriginalPrice] [varchar](255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL,
> [SalePrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [LocalizedSalePrice] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL,
> [Availability] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [Weight] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
> [OptionLists] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> SET ANSI_PADDING OFF
> Code I am using:
> SELECT [ProductID]
> ,[Code]
> ,[ProductDesc]
> ,[Url]
> ,[Orderable]
> ,[Taxable]
> ,[HTMLPath]
> ,[Caption]
> ,[Thumb]
> ,[Picture]
> ,[BasePrice]
> ,[LocalizedBasePrice]
> ,[OriginalPrice]
> ,[LocalizedOriginalPrice]
> ,[SalePrice]
> ,[LocalizedSalePrice]
> ,[Availability]
> ,[Weight]
> ,[OptionLists]
> FROM [yahoostore].[dbo].[PRODUCTS]
> where Url = 'NULL'
> Thank you
> Dee
>
> "Dee" wrote:
|||Thank you.
I was putting is null as 'is null' which did not work. It does work this way.
"Liliya Huff" wrote:
[vbcol=seagreen]
> here you are comparing with the string "NULL"
> FROM [yahoostore].[dbo].[PRODUCTS]
> where Url = 'NULL'
> if you want to find null,
> where Url is NULL
> --
> Thanks, Liliya
>
> "Dee" wrote: