For examples, lets say I need to do set operations and joins on below 3 result sets.

I don't want to write a composite query or use temporary database tables. Something which would enable faster data analysis using broken-down sets of data like below from a large database. Like as if you could join two result sets in toad or SQL developer with the help of a GUI.

SQL Query 1 result: (T1)

Col1 Col2 
A    1
B    2

Query 2 result: (T2)

Col3 Col4 
1    #
2    *

Query 3 result: (T3)


Required output is

 Select T1.col1,T2.col4 
        from T1,T2 
        where T1.col2=T2.col3 
        and T1.col2 in (Select T3.col5 from T3)

(Not necessary that the tool should generate SQL like above to process the result sets, SQL is only for conveying the logic.)

Required result:

Col1  Col4
1    #
  • So you want a GUI that writes queries. Like Access query builder? Aug 6, 2014 at 6:59
  • Yes I want a GUI to write queries, and also do further operations on the result sets of those queries (latter is the focus)
    – dbza
    Aug 6, 2014 at 16:44
  • moved to software recommendations softwarerecs.stackexchange.com/questions/10458/… since flagged here. Admins please help if this could be linked/moved to avoid duplicates , i have no idea if it is possible.
    – dbza
    Aug 6, 2014 at 19:07
  • it has been put on hold, so it's ok. Thank you for letting us know. Aug 6, 2014 at 19:11

2 Answers 2


It seems to me that you might want one of two things to do what you require.

1) could be global temporary tables (GTTs), see here or here. Check out the Oracle docco here for drier technical detail.

2) might be CTEs - Common Table Expressions (or subquery factoring as Oracle calls it) - also known as the "WITH CLAUSE". Howtos are here and here - (again, rather dry) Oracle docco is here.

One answer to your question is that every tool which supports Oracle supports "using SQL query result sets as temporary tables for further analysis and set operations" - this capacity is native to Oracle and available to all.

As for doing this with a GUI and dragging and dropping lines between either GTTs or CTEs - I'm not sure such a tool exists - but you could experiment with creating your GTT/CTE as a real concrete table and then do the Drag'n'Drop bit and when you're finally getting the result you want, you could just use the GUI tool generated SQL. Better though to learn SQL and be able to do this without a GUI.

  • sorry I had mentioned no temp tables. Even CTEs wouldn't do. I need to off-load the DB and do it locally.
    – dbza
    Aug 6, 2014 at 19:22
  • 1
    Again, just a thought, but maybe Oracle XE for local work? The SQL and PL/SQL is standard - XE has size, memory and higher edition restrictions, but might just be what you're looking for?
    – Vérace
    Aug 6, 2014 at 19:50
  • No not really :) I have a huge db2 database on which I run queries and store result sets for manual analysis. More details are on the question in SR. Thanks for helping to make it more clear though!
    – dbza
    Aug 6, 2014 at 21:12

I agree that a temporary table (or "hash table" # in SQL Server speak) will do what you want.

If you want to do this graphically I can recommend a product called "Lavastorm Analytics BRE". There is a free desktop version and it will allow you to view datasets on the fly. It can also do a few things you can't do with conventional SQL such as an inner and outer join in a single query.

  • Thanks Peter, I'll try using it and see if it fits my reqmt
    – dbza
    Aug 6, 2014 at 19:21
  • Be aware that temporary tables (#Tables) will disappear when you end your SQL Server session and you will loose all data in them. Aug 6, 2014 at 21:49

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