A WHERE_IN query with a composite key?

Question

Let's say I have a table with a two-component composite key and 4 entries, for example:

KEY_PART_1 KEY_PART_2 A 1 B 1 C 2 C 3 

I want to write dynamic SQL to select only records B, 1 and C, 2 using the WHERE IN clause, without choosing A, 1 or C, 3.

Is there a way to do this without a temporary table?

Not that it matters, but we are currently using Oracle and hope to move to PostgreSQL soon.

Answer 1

This syntax works for Oracle and PostgreSQL:

 SELECT * FROM table_name WHERE (key_part_1, key_part_2) IN ( ('B',1), ('C',2) ); 

Answer 2

Following @Justin Cave's answer, here is a small test case showing that Oracle is executing an INDEX RANGE SCAN followed by an INLIST ITERATOR . > for the next filter predicate :

 WHERE (key_part_1, key_part_2) IN ( ('B',1), ('C',2) ) 

Customization

 SQL> CREATE TABLE t(key1 VARCHAR2(1), key2 NUMBER); Table created. SQL> SQL> INSERT INTO t VALUES('A', 1); 1 row created. SQL> INSERT INTO t VALUES('B', 1); 1 row created. SQL> INSERT INTO t VALUES('C', 2); 1 row created. SQL> INSERT INTO t VALUES('C', 3); 1 row created. SQL> SQL> COMMIT; Commit complete. SQL> 

A composite index on key1 and key2 :

 SQL> CREATE INDEX t_idx ON t(key1, key2); Index created. SQL> 

Collect statistics:

 SQL> EXEC DBMS_STATS.gather_table_stats('LALIT', 'T'); PL/SQL procedure successfully completed. SQL> 

Run request:

 SQL> SELECT * FROM t 2 WHERE (key1, key2) IN ( ('B',1), ('C',2) ); K KEY2 - ---------- B 1 C 2 SQL> 

Thus, he gives the correct result.

See the explanation plan:

Case No. 1 A pair of value keys in the same index order. Leading key in front.

 SQL> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2301620486 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 10 | 1 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | |* 2 | INDEX RANGE SCAN| T_IDX | 2 | 10 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------------------------------------------------- 2 - access(("KEY1"='B' AND "KEY2"=1 OR "KEY1"='C' AND "KEY2"=2)) 14 rows selected. 

Case No. 2 A pair of value keys in the opposite order of the index. Lead key in the opposite direction.

 SQL> EXPLAIN PLAN FOR SELECT * FROM t 2 WHERE (key2, key1) IN ( (1, 'B'), (2, 'C') ); Explained. SQL> SQL> SELECT * FROM TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 2301620486 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 10 | 1 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | |* 2 | INDEX RANGE SCAN| T_IDX | 2 | 10 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- --------------------------------------------------- 2 - access(("KEY1"='B' AND "KEY2"=1 OR "KEY1"='C' AND "KEY2"=2)) 14 rows selected. 

In both cases, Oracle uses an index .

Answer 3

I'm not sure, but I think you want something similar that works for almost all RDBMSs:

 select KEY_PART_1, KEY_PART_2 from your_table where KEY_PART_1='B' and KEY_PART_2 = '1' UNION select KEY_PART_1, KEY_PART_2 from your_table where KEY_PART_1='C' and KEY_PART_2 = '2'