How to simplify this Sql query

Question

The Table - Query has 2 columns (functionId, depFunctionId)

I want all values that are either in functionid or in depfunctionid

I am using this:

select distinct depfunctionid from Query
union 
select distinct functionid from Query

How to do it better?

Answer 1

I think that's the best you'll get.

Answer 2

Thats as good as it gets I think...

Answer 3

I am almost sure you can loose the distinct's. When you use UNION instead of UNION ALL, duplicated results are thrown away.

It all depends on how heavy your inline view query is. The key for a better perfomance would be to execute only once, but that is not possible given the data that it returns.

If you do it like this :

select depfunctionid , functionid from Query
group by depfunctionid , functionid

It is very likely that you'll get repeated results for depfunctionid or functionid.

I may be wrong, but it seems to me that you're trying to retrieve a tree of dependencies. If thats the case, I personally would try to use a materialized path approach.

If the materialized path is stored in a self referencing table name, I would retrieve the tree using something like

select asrt2.function_id
from   a_self_referencig_table asrt1,
       a_self_referencig_table asrt2
where  asrt1.function_name = 'blah function'
and    asrt2.materialized_path like (asrt1.materialized_path || '%')
order  by asrt2.materialized_path, asrt2.some_child_node_ordering_column

This would retrieved the whole tree in the proper order. What sucks is having to construct the materialized path based on the function_id and parent_function_id (or in your case, functionid and depfunctionid), but a trigger could take care of it quite easily.

Answer 4

Lose the DISTINCT clauses, as your UNION (vs UNION ALL) will take care of removing duplicates.

An alternative - but perhaps less clear and probably with the same execution plan - would be to do a FULL JOIN across the 2 columns.

SELECT 
    COALESCE(Query1.FunctionId, Query2.DepFunctionId) as FunctionId
FROM Query as Query1
FULL OUTER JOIN Query as Query2 ON
    Query1.FunctionId = Query2.DepFunctionId