How to execute a SELECT on a table with a list of composite keys?

Question

If I have a table with columns like

ID1 ID2 VAL1 VAL2

Where ID1,ID2 makes the primary key index.

How do I execute any SQL statements for specific ID1,ID2 values?

Example: I have table with records that has PK (1,1) (1,2) (2,4) (2,1) (3,1) (3,2) (3,5)

I want to only select records with (1,1) (1,2) (3,5) (3,2)

A SELECT query with

SELECT * FROM tbl1 WHERE ID1 IN (1,3) AND ID2 IN (1,2,5)

will yield undesired result: (3,1). So what's the best way to do this?

I am looking for an overall answer for SQL, but if it is dependent on the DBMS, I would like to know how to do so in PostgreSQL and MySQL.

Martin Smith · Accepted Answer · 2011-10-23T10:01:09.020

2

Both MySQL and PostgreSQL support tuples in IN as below.

SELECT * 
FROM tbl1
WHERE (ID1,ID2) IN ((1,1) (1,2) (2,4) (2,1) (3,1) (3,2) (3,5));

edited Oct 23 '11 at 10:01

answered Oct 23 '11 at 09:53

Martin Smith

438,706
87
741
845

This is what I'm looking for. PostgreSQL also supports this syntax. However I'm wondering if it adheres to the left most prefix rules to utilise the primary key index. – Oct 23 '11 at 10:01
@RonaldChan - You'd have to look at the explain plans. Can't imagine the construct would cause any particular problems to the respective optimisers but don't know for sure. – Martin Smith Oct 23 '11 at 10:03
1

Thanks. It seems to be the case at least in Postgres. – Oct 23 '11 at 10:06
@RonaldChan: The primary key creates a multi-column on `(ID1, ID)`. As long as both columns are involved in the condition the index will be used equally effective. I wrote more, plus link [here](http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation/7774879#7774879). – Erwin Brandstetter Oct 23 '11 at 10:11
@ErwinBrandstetter - Nice answer in the link. I missed that first time round! – Martin Smith Oct 23 '11 at 10:17

score 0 · Answer 2 · answered Oct 23 '11 at 09:52

0

You can do that with and and or:

SELECT  * 
FROM    tbl1
WHERE   ID1 = 1 AND ID2 = 1 OR
        ID1 = 1 AND ID2 = 2 OR
        ID1 = 3 AND ID2 = 5 OR
        ID1 = 2 AND ID2 = 2

answered Oct 23 '11 at 09:52

Andomar

232,371
49
380
404

Presumably this is the only way? And it would still use the primary key index? It suddenly seems like SQL has an inefficient language barrier when dealing with composite keys. – Oct 23 '11 at 09:56

score 0 · Answer 3 · answered Oct 23 '11 at 09:53

0

This is probably the most compact way of doing what you requested:

SELECT *
FROM tbl1
WHERE (ID1 = 1 AND (ID2 = 1 OR ID2 = 2))
   OR (ID1 = 3 AND (ID2 = 5 OR ID2 = 2));

answered Oct 23 '11 at 09:53

Red Orca

4,755
1
15
12

score 0 · Answer 4 · answered Oct 23 '11 at 09:58

0

SELECT * FROM tbl1 WHERE ID1 = 1 AND ID2 IN (1,2)
UNION
SELECT * FROM tbl1 WHERE ID1 = 3 AND ID2 IN (5,2)

OR

SELECT * FROM tbl1 WHERE (ID1 = 1 AND ID2 IN (1,2)) OR (ID1 = 3 AND ID2 IN (5,2))

answered Oct 23 '11 at 09:58

Neel Basu

12,638
12
82
146

Robert · Answer 5 · 2011-10-23T15:45:47.690

0

You could also try

SELECT
   *
FROM
  tbl1
  JOIN
  (
    SELECT 1 AS ID1, 1 AS ID2
    UNION ALL
    SELECT 1 AS ID1, 2 AS ID2
    UNION ALL
    SELECT 2 AS ID1, 4 AS ID2
    UNION ALL
    SELECT 2 AS ID1, 1 AS ID2
    UNION ALL
    SELECT 3 AS ID1, 1 AS ID2
    UNION ALL
    SELECT 3 AS ID1, 2 AS ID2
    UNION ALL
    SELECT 3 AS ID1, 5 AS ID2
  ) as keylist ON tbl1.ID1 = keylist.ID1 AND tbl1.ID2 = keylist.ID2

edited Oct 23 '11 at 15:45

answered Oct 23 '11 at 10:02

Robert

3,328
2
24
25

If anybody should want to use this monstrous construct, be sure to make it [UNION ALL](http://www.postgresql.org/docs/9.1/interactive/sql-select.html#SQL-UNION). Also, there is no need to provide column alias in any but the first union-`SELECT`. – Erwin Brandstetter Oct 23 '11 at 10:18
@ErwinBrandstetter thanks for the UNION ALL heads up, i forgot the negligible performance benefit you would get in this case as the results set is so small. The reason i leave the field names in is for readability. This is because it is my belief that the objective of the code should be clear within the actual code. A programmer should only focus on performance when this is the improvement required.. – Robert Oct 23 '11 at 16:01

How to execute a SELECT on a table with a list of composite keys?

5 Answers5