CODEWITHSUNDEEP
sql-servert-sql
user5439827
user5439827

Reputation:

Table Variable and NULLS

I have run the below code but NULL do not show in the final query result. I can see that the NULL were inserted in the temp table. When I call the variable in the query, NULL values do not show up. 

USE [AdventureWorks2014]; 
GO  

SET NOCOUNT ON; 
SET STATISTICS IO ON;  

DECLARE @values TABLE 
(     
 Value VARCHAR(MAX) NULL 
)  

INSERT INTO @values 
VALUES (NULL), ('Black'), ('Blue'), ('Grey'), ('Multi'), ('Red'), ('Silver'), ('Silver'), ('Black'), ('White'), ('Yellow')  --SELECT Value FROM @values; 

SELECT Production.Product.Name, Production.Product.ProductNumber,   Production.Product.Color, Production.Product.ListPrice,   Production.Product.SIZE, Production.Product.[Weight],   Production.Product.ProductLine, Production.Product.Class,   Production.Product.STYLE, Production.ProductPhoto.ThumbNailPhoto,   Production.ProductPhoto.LargePhoto FROM Production.Product INNER JOIN Production.ProductProductPhoto   ON Production.Product.ProductID =     Production.ProductProductPhoto.ProductID   INNER JOIN Production.ProductPhoto   ON Production.ProductProductPhoto.ProductPhotoID =     Production.ProductPhoto.ProductPhotoID 
--BEGIN CALLOUT A 
WHERE --Production.Product.Color IS NOT NULL 
(Production.Product.Color IN (SELECT Value FROM @values)); 
--END CALLOUT A 
GO

Upvotes: 2

Views: 2134

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

Use INTERSECT

WHERE 
  exists (SELECT Production.Product.Color INTERSECT SELECT Value FROM @values)

Upvotes: 0

Arkadiusz
Arkadiusz

Reputation: 489

SET NOCOUNT ON; 
SET STATISTICS IO ON;  

DECLARE @values TABLE 
(     
 Value VARCHAR(MAX) NULL 
)  

INSERT INTO @values 
VALUES (NULL), ('Black'), ('Blue'), ('Grey'), ('Multi'), ('Red'), ('Silver'), ('Silver'), ('Black'), ('White'), ('Yellow')  --SELECT Value FROM @values; 

SELECT Production.Product.Name, Production.Product.ProductNumber,   Production.Product.Color, Production.Product.ListPrice,   Production.Product.SIZE, Production.Product.[Weight],   Production.Product.ProductLine, Production.Product.Class,   Production.Product.STYLE, Production.ProductPhoto.ThumbNailPhoto,   Production.ProductPhoto.LargePhoto 
FROM Production.Product 
INNER JOIN Production.ProductProductPhoto   ON Production.Product.ProductID =     Production.ProductProductPhoto.ProductID   
INNER JOIN Production.ProductPhoto   ON Production.ProductProductPhoto.ProductPhotoID =     Production.ProductPhoto.ProductPhotoID 
--BEGIN CALLOUT A 
WHERE --Production.Product.Color IS NOT NULL 
(ISNULL(Production.Product.Color,'EMPTY') IN (SELECT ISNULL(Value,'EMPTY') FROM @values)); 
--END CALLOUT A 
GO

Upvotes: 0

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34581

An IN statement will be executed identically to (field = val1) OR (field = val2) OR (field = val3). Putting a NULL in there will boil down to (field = NULL), which naturally won't work.

Instead of inserting a NULL into @values, try this:

WHERE (Production.Product.Color IN (SELECT Value FROM @values))
   OR (Production.Product.Color IS NULL)

Upvotes: 1

Related Questions