I’m a keyboard ‘ninja.’ I know how to get a lot of characters to splat up on my screen in as few keystrokes as possible. Yet sometimes I do fall back to the mouse. Cue the command-line SQL*Plus diehards rolling over in their graves.

One of my favorite tricks is the ability to drag 2 or more tables into the worksheet and have SQL Developer automatically create a SELECT with the WHERE clause joins pre-built based on existing foreign key definitions.

Example: Let’s build a massive query against the Sales History (SH) sample schema.

Select 2 or more tables, ctrl+drag and drop to the Worksheet

Go with ‘Select’ and ‘Join.’ I also recommend you leave the ‘Prompt every time’ box enabled as you may want to build a DML statement from time to time as well.

After I click ‘Apply,’ here is what I’m left with:

That's a lot of typing y'all!

There’s a few things I really like here

First, there is no SELECT * in this statement. SQL Developer is throwing all those column names in my face. The ‘*’ is just too convenient and easy for me to overlook. It’s expensive to query data you don’t need. This messy query is just begging to be trimmed down.

Second, the tables are auto-aliased. Maybe I don’t like the aliases, but they are aliased so it will run out of the box.

Trim the SELECT, Use Better Table Aliases

No need to use SEARCH and REPLACE. No need to move your mouse to delete unwanted columns. Just click here.

It's there to HELP you

Uncheck All

Mouse right click on your table, and select ‘Uncheck All.’ Then go back and add what you REALLY want.

TMI?

Lather, rinse, and repeat.

Use Better Table Aliases

‘A’ is not very useful. But maybe ‘PROD’ is. Or maybe no alias is the best alias. Mouse right click on the table and choose ‘Properties.’ Provide a new alias or remove it completely.

Lather, rinse, and repeat.

Now toggle back to the Worksheet

A little better?

Now you are left to your evil SQL coding. Add your additional predicates, subqueries, WITH-ify it, etc. Of course you can rely on the Query Builder for doing this sort of work as well, but I find the biggest bang for your productivity buck is to let it simply build out the ‘framework’ of your SQL statement and then finish it off in the worksheet.

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.

2 Comments

  1. How can you add a subquery using the query builder and not by just typing it into the worksheet? I see that if you type it into the worksheet, then a second tab appears in the query builder. But, how can you start a new tab for a subquery in the query builder?

Write A Comment