Search code examples
sqloracle-databasejoinintersectrelational-division

How to do intersection on a composition table

I have a simple SQL relational model with a many to many relation. Here is the composition table

 ___________________________
| object1_id  | object2_id  |
|---------------------------|

I would like to know all the object1 that are common to a set of object2. My basic feeling is to do a request like this

SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_1>
INTERSECT 
SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_2>

And if I have N object2 in the set, I'll do N INTERSECT

SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_1>
INTERSECT 
SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_2>
...
INTERSECT 
SELECT c.object1_id FROM composition c WHERE c.object2_id = <given_id_N>

However, it doesn't look very optimized. Can you help me ? I am not really a SQL expert. I think I could use a JOIN to do that.

Sample

 ___________________________
| object1_id  | object2_id  |
|---------------------------|
|         10  |           1 |
|         11  |           1 |
|         10  |           2 |
|         12  |           2 |
|         10  |           3 |
|         11  |           3 |
|         13  |           3 |

Example

  • { object2_id set } => { expected object1_id }
  • { 1, 2 } => { 10 }
  • { 1, 3 } => { 10, 11 }
  • { 1, 2, 3 } => { 10 }
Solution

  • In terms of performance your query looks OK. Have you measured it to see if there really is a problem?

    If (object1_id, object2_id) is unique then you can write the query more concisely as follows:

    SELECT object1_id
FROM composition
WHERE object2_id IN (id1, id2, ..., id6)
GROUP BY object1_id
HAVING COUNT(*) = 6

    Note that the 6 is the number of provided IDs. This should be changed if a different number of IDs is provided. You would have to measure the actual performance on your dadta to see if this gives any speed increase.

    If you can't assume uniqueness then this should work:

    SELECT object1_id
FROM composition
WHERE object2_id IN (id1, id2, ..., id6)
GROUP BY object1_id
HAVING COUNT(DISTINCT object2_id) = 6

    The most important thing though is to make sure you have appropriate indexes on your table! This is far more important than whether you write one query or the other.