Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
SQL Server - NULL values with IN and NOT IN (Solution)
-------------------------------------------------------------------------------- -- Create source data -------------------------------------------------------------------------------- CREATE TABLE #ValueList ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY, val_1 VARCHAR(10), metric INT ); INSERT INTO #ValueList (val_1,metric) VALUES('a', 10), ('b', 20), ('c', 30), (NULL, 40) ; -- View our sample data SELECT 'Show All Data' AS [marker]; SELECT * FROM #ValueList ; -------------------------------------------------------------------------------- -- Solution: Using COALESCE -------------------------------------------------------------------------------- SELECT 'Solution 1' AS [marker]; SELECT * FROM #ValueList WHERE COALESCE(val_1, 'Z') NOT IN ('a', 'b') ; -------------------------------------------------------------------------------- -- Solution: Using an OR condition to allow NULLs -------------------------------------------------------------------------------- SELECT 'Solution 2' AS [marker]; SELECT * FROM #ValueList WHERE( val_1 NOT IN ('a', 'b') OR val_1 IS NULL ) ; -------------------------------------------------------------------------------- -- Solution: Using WHERE NOT EXISTS and a lookup table -------------------------------------------------------------------------------- SELECT 'Solution 3' AS [marker]; -- Create a lookup list -------------------------------------------------------------------------------- CREATE TABLE #LookupList ( val_1 VARCHAR(10) NOT NULL PRIMARY KEY ); INSERT INTO #LookupList (val_1) VALUES('a'), ('b') ; -- Filter using WHERE (NOT) EXISTS -------------------------------------------------------------------------------- SELECT * FROM #ValueList vl WHERE NOT EXISTS (SELECT * FROM #LookupList ll WHERE ll.val_1 = vl.val_1 ) ; SELECT * FROM #ValueList vl WHERE EXISTS (SELECT * FROM #LookupList ll WHERE ll.val_1 = vl.val_1 ) ;
run
|
edit
|
history
|
help
0
table emp
Función Borrar carácter inicial repetido
table creation
Sql Queries
Create Index On Viiew
FIGURA5.2
TSQL - Unique string generator
BC160401693
SQL SERVER 2012 how to split records in a column separated by delimiter
Trabalho.Agrecação