Click AND Type your Queries with the Integrated Query Builder

I was perusing the SQL Developer Feature Requests Exchange and was not surprised to see this – a request to have the Query Builder disabled. My favorite (and most predictable) comment among the voters for this request is something along the lines of

‘feature totally useless for us professionals’

Let’s explore that for a second. First of all, I’m not judging this user’s point of view. It’s probably quite prevalent amongst the experienced database folks out there. This is not surprising to me in the least, and I’ve probably heard it about a 100 times. So maybe MY perspective is warped, but that’s just me!

The good news here for me was that they wanted to make the Query Builder workspace OPTIONAL, so as to not hog up processing and desktop resources and real estate while using the worksheet. I’m all for options, and I don’t see a problem with making this widget go away if the user so desires.

But, I do think that wanting to get rid of it because it brings nothing to the table is worthy of a bit of debate. Instead of rehashing that debate, let me send you down a really quick trip of memory lane. Be sure to come back!

If you’re a loyal reader (thanks Mom), then some of this may recall my previous rant – Click vs Type SQL.

So, even if you vehemently disagree with me, let me at least give you a quick tour of how this works.

Query Builder in v3.1EA

I’m using the Early Adopter release of our v3.1 product. You can go download and play with this today if you’d like.

When you open a worksheet to write your queries, you will notice a ‘Query Builder’ page adjacent to the worksheet label. As I type my query in the worksheet, SQL Developer will attempt to ‘model’ this query into a visual rendering. For anyone who has used Access before, drawing a query is pretty familiar, if not frustrating, experience. I think we can all agree that the goal here is to aim way higher than the Access bar.

Some developers will just query ‘everything’ just in case they decide they need the data later. Folks new to databases and SQL will love how easy it is to get data back by using the splat/asterisk/wildcard character. Meanwhile the database and network suffer with the extra IO and network traffic…but I digress.

Some people are keyboard folks. They see vi as their best friend, and are more comfortable looking at a green screen than anything that sometimes requires the use of a mouse. SQL Developer’s editor allows you to type all your queries by hand if you want. It will even attempt to finish your SELECTs, FROMs, and WHEREs. One nice touch – it will auto-write your JOIN clauses wherever you have a Foreign Key constraint available. Me like!

But, let’s say you do what I do – you write a big fat SELECT *, then decide to trim it back after you get the bulk of your query finished. Baaad. Well, maybe the Query Builder can help me here.

Instead of rewriting the SELECT line, I can just click over to the Query Builder panel and toggle all of the columns I need added. So in a matter of a few seconds, I go from

select * from employees, departments
where employees.department_id = departments.department_id
and departments.location_id not in (10, 100, 100);


FROM employees,
WHERE employees.DEPARTMENT_ID     = departments.DEPARTMENT_ID
AND (departments.LOCATION_ID NOT IN (10, 100, 100));

Now look, I realize this is a very trivial example, but I think it has some merit. Just a few clicks in a second or two has probably saved me a minute or so of typing.

Here’s what that looks like in SQL Developer

Type, type, type - all day long!

The trick is to click!

Ok, now I click into the fields I want to have in my SELECT field

Click the fields

And I get my query in the worksheet updated auto-magically!

I'm a lazy typer. Aren't most developers lazy?

What is SQL Developer Adding to the Community Here?

Most, if not all, database IDEs have a visual query builder for SQL statements. However, they are always implemented as a separate window or workflow from the primary scratchpad or editor. SQL Developer is saying, “Hey, we’re going to integrate this with the editor.” I like this for a couple of reasons.

1 – It makes it MUCH harder to miss. We could spend months going over hidden features as ‘tips and tricks’ because most software users don’t dig very deep when using ANY program.

B – It might make the advanced database people think for a second about whether they’re missing something. I’ll probably lose this argument eventually, but I’m not giving up just quite yet.