1

I have two complex queries and I have to filter out the results from the second one, it looks something like this:

SELECT STH INTO #temp_table_1

FROM TABLE1,
TABLE2,
TABLE3

WHERE
TABLE1.ID = TABLE2.ID,
TABLE2.ID = TABLE3.ID,
TABLE3.STATUS = 'A';

SELECT STH INTO #temp_table_2

FROM TABLE1,
TABLE2,
TABLE3

WHERE
TABLE1.ID = TABLE2.ID,
TABLE2.ID = TABLE3.ID,
TABLE3.STATUS = 'B';

SELECT STH FROM #temp_table_1 t1
WHERE NOT EXIST (SELECT 1 FROM #temp_table_2 t2 WHERE t2.COL = t1.COL);

The problem is that I have to get rid of those temporary tables and write everyting in one query. But if I substitute subqueries for #temp_table_1 and #temp_table_2, it just takes too much time.

Is it possible to achieve almost the same runtime using only single query? Someone told me that I could use UNION, but didn't elaborate on that further. Is it true? This is Sybase IQ.

  • How about a CTE (Common Table Expression)? dcx.sybase.com/1100/en/dbusage_en11/… – MguerraTorres Jun 10 '18 at 12:22
  • setting aside the fact that COL does not exist in either #temp table ... is STH unique within each #temp table? if STH is unique, you may want to look at something like select STH,count(*) from T1,T2,T3 where ... STATUS in ('A','B') group by STH having count(*) = 1; if you can't live with the count(*) as a column, convert to a sub-query (CTH or derived table) and have the top-level query select STH from <dervied_table> where counter=1 – markp Jun 10 '18 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.