I’ve talked about the query builder a bit here on these pages, but sometimes a quick video can do a much better job demonstrating these concepts.

The video is about 8 minutes and covers:

  • writing a query the old-fashioned way
  • writing a query with drag-and-drop from the tree
  • using the Query Builder

At about 5 minutes in, I talk about why I think ANY SQL Developer user can get value from the Query Builder – not just the n00bs.

What I Didn’t Show

I showed how nice it is when you ALREADY have referential integrity defined and enforced in the database via foreign key constraints. But sometimes…well, sometimes those aren’t there.

You can still use the Query Builder.

And, you can modify the joins to do OUTER joins.

Drag and drop between columns to create the join - double click on the join to make it left or right outer
Drag and drop between columns to create the join – double click on the join to make it left or right outer

So next time you have to write that beautiful 15 table join SELECT, why not give the Query Builder a try?

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

11 Comments

  1. 1’52” how did you select multiple tables? I tried it by pressing ctrl when select the 2nd table, but it always give the structure of the 2nd table and 1st table will be de-selected.

  2. Everett Pence Reply

    Hi Jeff,
    I am using SQL Developer 4.1.3.20 and when in the Query Builder section, the right mouse does not bring up a menu so I cannot select uncheck or check all for example. Also, when opening a saved query and then going to query builder, all of the tables are shrunk with no fields showing. They only show up if I modify the query on the worksheet. Have you experienced these issues? I have searched Google to no avail.

    Thank you,
    Everett

  3. I am missing Query Builder from Oracle SQL Developer ver 1.5.5. How do I get it to show?

  4. Hi, when i use ‘Querie builder’ it delete all comments from select. Is it possible to configure it somehow that it will leave comments?

    For example:
    SELECT * –aaaa
    FROM REGIST /*aaa*/;

    will convert to:
    SELECT REGIST.NR, REGIST.CODE FROM REGIST

    but it would be nice if it could convert to something like this:
    SELECT REGIST.NR, REGIST.CODE
    –aaaa
    FROM REGIST /*aaa*/

  5. Sabine Heimsath Reply

    Hi Jeff,

    is it possible to zoom in and out in the Query Builder? For a rough layout it would be great to have an overview of all tables in the query instead of constantly scrolling around.

  6. tom hamaekers Reply

    Hi,

    is it possible to print from the query builder. ( or export )

  7. Yet one more reason to put actual FKs in your database!

    It is also worth noting that the same Query Builder function is available in Data Modeler for when you define a view. I use it extensively for building reporting views on my data warehouse models. That is way easier than writing all those complex joins.

Write A Comment