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
DeePlease 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...
> >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
>
>|||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:
> 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...
> > >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
> >
> >
> >|||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:
> 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:
> >
> > 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...
> > > >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
> > >
> > >
> > >|||Thank you.
I was putting is null as 'is null' which did not work. It does work this way.
"Liliya Huff" 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:
> > 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:
> >
> > >
> > > 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...
> > > > >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
> > > >
> > > >
> > > >