Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Generally, the keyword here is "relational division". We have assembled a whole arsenal of queries to solve your case exactly under this related question on stackoverflow. –  Erwin Brandstetter Nov 10 '12 at 6:02
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
The performance difference depends very much on data distribution. I could easily set up a test case when the JOIN is faster than the double EXISTS. –  dezso Nov 9 '12 at 16:10
    
Isn't it better to use SELECT 1 instead of SELECT *? Or do modern query engines treat them the same? I was always taught to use SELECT 1 for EXISTS queries because it's that much less fetching the engine has to do. –  Bacon Bits Nov 12 '12 at 4:32
    
Every database engine I've done any serious work with handles select * in an EXISTS clause the same was as select [column] or select [constant]: the column selection never actually happens. I could see some 1980s DB2 implementation having trouble with this, but if there is a modern RDBMS which fails to do that, I would honestly be shocked, simply because it's such an easy optimization. –  Kevin Feasel Nov 12 '12 at 12:24
add comment

The easiest shortest way to go is

SELECT c.* 
FROM 
    cars AS c
    JOIN Imperfections as i1 ON c.uid = i1.uid AND i1.imperfection = 1
    JOIN Imperfections as i2 ON c.uid = i2.uid AND i2.imperfection = 2
;

The (INNER) JOINs test for existence themselves.

The next idea would be the same that Kevin describes in the his answer, so I won't include it here.

share|improve this answer
    
Thanks to both you and @Kevin. I ran both through query analyzer, and both are very comparable in performance. However, I like Kevin's implementation from a development standpoint - since this query is going to be dynamically generated and executed, while I'm "looping over the imperfections" to include, I'll only have to change one variable value as I build the SQL string. Thanks! –  Jmoney38 Nov 9 '12 at 15:45
1  
This holds if (uid, imperfection) is UNIQUE, which would be plausible here, but has not been defined. There could be subtypes of imperfections ... then you'd need a DISTINCT or GROUP BY. As far as "shortest" goes, USING (uid) is shorter than ON c.uid = i1.uid. Imperfections would go into a WHERE clause then. –  Erwin Brandstetter Nov 10 '12 at 5:13
    
@Erwin: Do you really like the use of USING in production? I don't appreciate it much. –  ypercube Nov 10 '12 at 11:29
    
@ypercube what's the problem with it? In certain cases it improves readability, and when one has to change the table structures, s/he has to check dependencies as well... –  dezso Nov 10 '12 at 11:33
    
@ypercube: It is standard SQL and I use it. I might avoid it in complex queries where I don't want to limit the options of the planner to reorder joins, but that's rare. After my recent question nothing substantial surfaced, and I have yet to see any problems. So AFAICS, USING is generally save to use. –  Erwin Brandstetter Nov 10 '12 at 11:49
add comment
SELECT * 
FROM cars AS T1 
WHERE 
    EXISTS 
    (
        SELECT 1 FROM Imperfections as T2 
        WHERE T1.uid = T2.uid AND imperfection IN(1,2)
        HAVING COUNT(DISTINCT imperfection) =2
     )
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.