Are these SQL queries equivalent? And which is better or is there a better option?

I am working on a Spring web application that utilizes hibernate to connect to a DB2 database. I am try to optimize a service method that gets called may times during a wed service call by reducing the number of DB queries.

So my question is whether or not this query

SELECT DISTINCT a.* FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.FK_ID 
LEFT JOIN TABLE_C c ON a.ID = c.FK_ID
LEFT JOIN TABLE_D d ON c.DATA_RQST_ID = d.ID
WHERE (b.REQUEST_ID = 1234 AND b.TYPE = 'TYPE_A')
OR (c.REQUEST_ID = 1234 AND (c.TYPE = 'TYPE_A' OR c.TYPE = 'TYPE_B'))

is equivalent/better then this query

SELECT * FROM TABLE_A a 
WHERE  a.ID IN 
(
    SELECT b.FK_ID FROM  TABLE_B b 
    WHERE b.REQUEST_ID = 1234 AND eb.TYPE = 'TYPE_A' 
)
OR a.ID IN 
(
    SELECT c.FK_ID FROM TABLE_C 
    WHERE ( c.REQUEST_ID = 1234 AND c.TYPE = 'TYPE_A' )
    OR 
        (
            c.TYPE = 'TYPE_B' AND c.REQUEST_ID IN 
                (
                    SELECT d.ID FROM TABLE_D d 
                    WHERE d.REQUEST_ID = 1234 AND v.TYPE = 'TYPE_A'
                )
        )
)

or is there a better option?

Both queries seem to run about the same time (<50ms) but that may depend on the resulting data. I would need to test more to know for sure.

The point of these two queries is for one of them to replace three other queries where their resulting data is processed in Java to get the required data.

I will also have to be able to convert the SQL query to HQL. I was struggling to convert the first query.

I have a feeling that I maybe wasting my time since the java objects for tables B and C are a one-to-many relationship in the object for table A and they are load by hibernate anyway. Meaning I may not be saving anytime in the long run. Is my thinking here correct?

Thanks!

Answer

If I understand correctly, exists would be the best solution:

SELECT a.*
FROM TABLE_A a
WHERE EXISTS (SELECT 1
              FROM TABLE_B b 
              WHERE a.ID = b.FK_ID AND b.REQUEST_ID = 1234 AND b.TYPE = 'TYPE_A'
             ) OR
      EXISTS (SELECT 1
              FROM TABLE_C c JOIN
                   TABLE_D d
                   ON c.DATA_RQST_ID = d.ID
              WHERE a.ID = c.FK_ID AND
                    c.REQUEST_ID = 1234 AND
                    (c.TYPE IN ('TYPE_A', 'TYPE_B'))
             );

One big gain is just in removing the select distinct.

Then for performance, you want indexes on table_b(fk_id, request_id, type_id) and table_c(fk_id, request_id, type, DATA_RQST_ID) and table_d(id).