Sql: How To Unite Fields Into A Vector In A Select Query?

What I mean is: ... select... where (a=1 and b=1) or (a=1 and b=2) or (a=2 and b=3) or(a=3 and b =2) can this be simplified to something like ...select... where (a,b) in ((1,1),(

Solution 1:

You didn't state your DBMS, but this:

where (a,b) in ((1,1),(1,2),(2,3),(3,2))

is valid (standard) SQL.

And it works (at least) in PostgreSQL, Oracle and MySQL:

SQLFiddle demo for PostgreSQL: http://sqlfiddle.com/#!12/ffbcb/1 SQLFiddle demo for Oracle: http://sqlfiddle.com/#!4/a42cb/1 SQLFiddle demo for MySQL: http://sqlfiddle.com/#!2/a42cb/1

As this is ANSI SQL other DBMS should support that too.

Solution 2:

For SQLServer2005+

Function CHECKSUM() calculates hash-value which is called as checksum. more info

SELECT *
FROM dbo.your_table t
WHERE CHECKSUM(a,b) in (CHECKSUM(1,1),CHECKSUM(1,2),CHECKSUM(2,3),CHECKSUM(3,2))