Tag Team your SQL w/the IDE & Visual Query Builder

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.

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:

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.

Uncheck All

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

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

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.