Many-to-one Subselection in single query

I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2.

To create a simple example, lets say T1 is Cars, and T2 is a table of imperfections. So, a car can have 0..N imperfections, and we store these imperfections in T2 as integers.

I would like to select * from only those cars in Cars that contain imperfections i1 AND i2.

Performing an OR instead is pretty easy:

SELECT * FROM cars AS T1 
  WHERE EXISTS (
    SELECT imperfection FROM Imperfections as T2 
      WHERE T1.uid = T2.uid AND (imperfection = 1 OR imperfection = 2)
  );

I've been trying set logic using intersection, but at this point, I'm wondering if I'm over complicating it.

You could have two separate exists clauses:

SELECT * 
FROM cars AS T1 
WHERE 
    EXISTS 
    (
        SELECT * FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection = 1
    )
    AND EXISTS
    (
        SELECT * FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection = 2
    );

This means two left semi joins to your Imperfections table, but if you have an index on UID and Imperfection, those should be trivial.

There are two advantages to using WHERE EXISTS over JOIN in this case. The first is that a left semi-join stops after the first match, so it can be quicker than a join (which needs to go through all records). The second is that if you somehow don't have good data constraints and have two records in Imperfections with the same UID and Imperfection, you could end up with duplicate rows back in your main query.

StackExchange Database Administrators Q#28431, answer score: 7