IN Operator

test_expression [ NOT ] IN
( subquery | expression [ ,...n ]
)

Bekannt ist, das man mit dem IN Operator prüfen kann, ob Werte in einer Spalte vorhanden sind:

where Spalte1 in (1,2,3)

das es auch andersherum geht, ist eher unbekannt. Obwohl nach Onlinehilfe die test_expression "nur" ein gültiger Ausdruck sein muss:

where 1 in (Spalte1, Spalte2, Spalte3)

siehe hierzu auch:

http://blogs.technet.com/wardpond/archive/2009/09/04/database-programming-did-you-know-in-can-do-this.aspx

http://sqlblog.com/blogs/denis_gobo/archive/2009/04/09/13186.aspx

Gültige Ausdrücke sind

{ constant | scalar_function | [ table_name. ] column | variable 

        | ( expression ) | ( scalar_subquery ) 

    | { unary_operator } expression 

    | expression { binary_operator } expression 

    | ranking_windowed_function | aggregate_windowed_function

}

also where (Ausdruck) in (Spalte1, Spalte2, Spalte3)

Aber der Ausdruck darf nicht mehr als eine Zeile und Spalte zurückgeben (skalarer Ausdruck). Mit einem kleinen Trick kann man aber trotzdem mehrere Werte übergeben. Dazu ein Beispiel mit Testdaten:

begin try drop table #t end try begin catch end catch;

select

identity(int, 1, 1) id,

*

into #t

from

(

values

(0,0,0,0,0,0), (1,0,0,0,0,0), (0,1,0,0,0,0),

(0,0,1,0,0,0), (0,0,0,1,0,0), (0,0,0,0,1,0),

(0,0,0,0,0,1), (2,0,0,0,0,0), (0,2,0,0,0,0),

(0,0,2,0,0,0), (0,0,0,2,0,0), (0,0,0,0,2,0),

(0,0,0,0,0,2), (3,0,0,0,0,0), (0,3,0,0,0,0),

(0,0,3,0,0,0), (0,0,0,3,0,0), (0,0,0,0,3,0),

(0,0,0,0,0,3)

) t(sp1, sp2, sp3, sp4, sp5, sp6);

Ein Crossjoin mit den zu überprüfenden Werten funktioniert:

select distinct id

from #t,

(

select arg

from (values (1), (2)) a(arg)

) t

where

arg in (sp1, sp2, sp3, sp4, sp5, sp6);

Noch ein anderes Beispiel, mit dem man alle Lottozahlen 6 aus 49 inkl. Zusatzzahl erzeugen kann (vielleicht weiß ja jemand, wie man damit den Jackpot knacken kann ;-) )

;with n as

(

select 1 n

union all

select n + 1

from n

where

n < 49

),

z as

(

select 1 z

union all

select z + 1

from z

where

z < 10

)

,lotto as

(

select

n1.n as Zahl_1,

n2.n as Zahl_2,

n3.n as Zahl_3,

n4.n as Zahl_4,

n5.n as Zahl_5,

n6.n as Zahl_6,

z.z as Zusatzzahl

from n n1, n n2, n n3, n n4, n n5, n n6, z

where

n1.n not in (n2.n, n3.n, n4.n, n5.n, n6.n) and

n2.n not in (n1.n, n3.n, n4.n, n5.n, n6.n) and

n3.n not in (n1.n, n2.n, n4.n, n5.n, n6.n) and

n4.n not in (n1.n, n2.n, n3.n, n5.n, n6.n) and

n5.n not in (n1.n, n2.n, n3.n, n4.n, n6.n) and

n6.n not in (n1.n, n2.n, n3.n, n4.n, n5.n) and

n2.n > n1.n and

n3.n > n2.n and

n4.n > n3.n and

n5.n > n4.n and

n6.n > n5.n

)

select top(1) * -- aus 139838160 Zeilen

from lotto

order by

newid();

Viele Grüße

Christoph Ingenhaag