CODEWITHSUNDEEP
sql
b_it_s
b_it_s

Reputation: 724

Improving query having two where ... in clauses

Is there a better (more efficient) way to make this query?

I don't want do search twice in the same query (SELECT PDATA1 ...)

SELECT package_ID
FROM t_package
WHERE parent_ID IN (SELECT PDATA1
                    FROM t_object
                    WHERE stereotype = 'Process' AND object_type = 'Package')
OR package_ID IN (SELECT PDATA1
                  FROM t_object
                  WHERE stereotype = 'Process' AND object_type = 'Package')

Upvotes: 1

Views: 62

Answers (5)

Guillermo Merino
Guillermo Merino

Reputation: 3257

What about:

SELECT package_ID
FROM t_package, t_object
WHERE  stereotype = 'Process' AND object_type = 'Package'
and parent_ID = PDATA1
or package_ID = PDATA1

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

It really depends on the dbms. Some dbms make better execution plans on EXISTS clauses than on IN clauses for instance.

Often a simple join is treated best. I don't like this solution too much, because your statement is much easier to read, but concerning speed, you may give this a try:

select distinct p.package_id
from t_package p
inner join t_object o on o.pdata1 in (p.package_id, p.parent_id)
  and o.stereotype = 'process' and o.object_type = 'package';

In your case you have some IDs already in the t_object table, other must be got from t_package. So you can also try UNION here. It may be faster, though I don't find that likely. It depends on the dbms, on data, indexes, etc.

select package_id
from t_package
where parent_id in 
(
  select pdata1
  from t_object
  where stereotype = 'process' and object_type = 'package'
)
union
select pdata1
from t_object
where stereotype = 'process' and object_type = 'package';

or with exists:

select package_id
from t_package
where exists
(
  select *
  from t_object
  where pdata1 = parent_id and stereotype = 'process' and object_type = 'package'
)
union
select pdata1
from t_object
where stereotype = 'process' and object_type = 'package';

Upvotes: 0

Andreas Eriksson
Andreas Eriksson

Reputation: 9027

I believe this can be accomplished entirely without subqueries.

SELECT
    DISTINCT(tp.package_ID)
FROM 
    t_package tp
INNER JOIN
    t_object tobj ON tobj.PDATA1 = tp.parent_ID OR tobj.PDATA1 = tp.package_ID
WHERE
    tobj.stereotype = 'Process' AND tobj.object_type = 'Package'

Upvotes: 1

Jim
Jim

Reputation: 6881

You can join to the t_object table twice; once to look up the parent_ID relationship and another to look up the package_ID relationship.

SELECT p.package_ID
FROM t_package p
LEFT JOIN t_object oParent
ON p.parent_ID = oParent.PDATA1
AND oParent.stereotype = 'Process' 
AND oParent.object_type = 'Package'
LEFT JOIN t_object oPackage
ON p.parent_ID = oParent.PDATA1
AND oPackage.stereotype = 'Process' 
AND oPackage.object_type = 'Package'
WHERE oParent.PDATA1 IS NOT NULL OR oPackage.PDATA1 IS NOT NULL

Upvotes: 0

attila
attila

Reputation: 2229

Use EXISTS, like this - 

SELECT package_ID
FROM t_package t
WHERE exists(SELECT *                        
FROM t_object o
    WHERE stereotype = 'Process'
    AND object_type = 'Package' 
    and (o.PDATA1=t.parent_ID 
    OR  o.PDATA1=t.package_ID)
)

Upvotes: 0

Related Questions