Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
SQL Server - NULL values with IN and NOT IN
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
-------------------------------------------------------------------------------- -- 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 ; -------------------------------------------------------------------------------- -- Hard coded list: NOT IN -------------------------------------------------------------------------------- SELECT 'Test 1' AS [marker]; SELECT * FROM #ValueList WHERE val_1 NOT IN ('a', 'b') -- Skips NULL ; SELECT 'Test 2' AS [marker]; SELECT * FROM #ValueList WHERE val_1 NOT IN ('a', 'b', NULL) -- Returns no results ; -------------------------------------------------------------------------------- -- Hard coded list: IN -------------------------------------------------------------------------------- SELECT 'Test 3' AS [marker]; SELECT * FROM #ValueList WHERE val_1 IN ('a', 'b') -- Skips NULL ; SELECT 'Test 4' AS [marker]; SELECT * FROM #ValueList WHERE val_1 IN ('a', 'b', NULL) -- Skips NULL ;
View schema
Execution time: 0,02 sec, rows selected: 14, rows affected: 4, absolute service time: 0,2 sec, absolute service time: 0,21 sec
edit mode
|
history
|
discussion
marker
1
Show All Data
ID
val_1
metric
1
1
a
10
2
2
b
20
3
3
c
30
4
4
NULL
40
marker
1
Test 1
ID
val_1
metric
1
3
c
30
marker
1
Test 2
marker
1
Test 3
ID
val_1
metric
1
1
a
10
2
2
b
20
marker
1
Test 4
ID
val_1
metric
1
1
a
10
2
2
b
20