Saturday, July 16, 2011

Multiple columns in Oracle IN clause 

How to use more than one column in Oracle IN clause:

CREATE TABLE t1 (id VARCHAR2(10), version NUMBER(2,1), name VARCHAR2(20));

INSERT INTO t1 VALUES ('1001', 1.1 ,'FIRST ELEMENT');
INSERT INTO t1 VALUES ('1001', 1.2 ,'FIRST ELEMENT');
INSERT INTO t1 VALUES ('1001', 1.3 ,'FIRST ELEMENT');
INSERT INTO t1 VALUES ('1001', 1.4 ,'FIRST ELEMENT');

INSERT INTO t1 VALUES ('1002', 1.1 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.2 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.3 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.4 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.5 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.6 ,'SECOND ELEMENT');
INSERT INTO t1 VALUES ('1002', 1.7 ,'SECOND ELEMENT');

SELECT id, version, name FROM t1 WHERE (id, version) IN ( (1001,1.1), (1002,1.6) );

No comments:

Post a Comment