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);
to
SELECT employees.FIRST_NAME, employees.LAST_NAME, employees.SALARY, departments.DEPARTMENT_NAME FROM employees, departments 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
Ok, now I click into the fields I want to have in my SELECT field
And I get my query in the worksheet updated auto-magically!
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.
23 Comments
I have tables A, B, C, D, E and F and relationship flows from AB—>C–>D–>E–>F. Is there a way that I only specify tables A and F and query builder works out the flow itself based on foreign keys and can provide me a sql?
Thanks in advance for help!
No, BC you have to bring in B,C,D, and E too…
Thanks! How cool a feature like that would be as it’s not a rocket science to work it out.
i am trying to develope db tool like sql developer and i did almost but the problem is how to generate sql query for query builder tables with joins..camn you plz help me…
Why build a tool when there are already hundreds of db tools out there, including ours? I can’t help you with that, sorry.
One more question: I typed a new query in the window I was using Query Builder and then switched to QB tab. But I get this message: query builder is not supported for this connection type
My target DB is Oracle itself.
Can you share your query so I can replicate/troubleshoot?
Sorry Jeff. I closed that query window and now I don’t remember – it was just a simple select * from a view/table statement using the only connection that I was already using successfully. Does the query builder work only after I run the query? What are the normal circumstances under which I get this connection not supported?
I ran across this message recently – this might explain it. In my case I had a worksheet open, and at some later point the associated connection was closed. When I reconnected I got my new worksheet, but the old worksheet was still without a connection. Simply choosing a connection from the drop-down on the worksheet put Query Builder back in business.
thx, works!
Jeff, thanks for pointing me to this wonderful features that could save a ton of time for me. Like you mentioned, before reading your article, I was one of those old-school boys who frown at query builder. Now I am trying to get my hands wet.
I am working with a Database which has thousands of tables. So I can’t really drag and drop them all together from left pane. Is there a way for me drag a new table into query table? I tried to do that and it hanged.
When I build multi-table query, I start with 2 tables and their join conditions and then add the next table, etc. How do I really enable this with the Query builder? I played around a little bit and figured out a way to do so. But I wanted to get it from the horse’s mouth
Dragging directly into the Query Builder panel has a known performance issue 🙁
Instead, drag your table into the worksheet. Choose the ‘SELECT’ option. Or do it for more than 1 table and choose the SELECT with JOIN option. THEN toggle to the Query Builder and finish it off…
Hi Jeff, thanks for the answer. When I drag my first table into worksheet, the full select statement is executed. Now, how I do add the next table and make the worksheet do the join between the table already there in the worksheet and the newly dragged one?
Add the second and following tables by dragging them to the query builder. If there’s a FK constraint between it and the existing tables, the join will be ‘drawn’ for you. If not, then you can manually associate the columns with a drag and drop of your mouse cursor from Table1.Column1 to Table2.Column2.
Hello Jeff,
Thanks for the post.
I wonder if you can help me to find an answer to: how to switch Query Builder from default ANSI SQL joins’ standard “FROM HR.EMPLOYEES INNER JOIN HR.DEPARTMENTS” to one I have used to “FROM HR.EMPLOYEES, HR.DEPARTMENTS WHERE ….”.
I noticed that if I start writing an SQL on “old/oracle” style Query Builder will keep using it. However I wonder how to set it to be a default for a new SQL.
Thanks,
Yury
We’ve made this configurable in the Data Modeler but not in the proper SQL Developer app. Needs to happen, should happen, and may happen 🙂 Will let you know!
THX Jeff. You are always very supportive 🙂
Hi Jeff. I have been using Oracle SQL Developer for about a year and it has completely eliminated my need for TOAD. I currently use SD to connect with Sprint’s Oracle data warehouses but looking to expand its connections to Microsoft SQL Server and Teradata. I’m working with Sprint’s IT department to resolve an issue we are having with available JDBC Drivers and its compatibility with Oracle SQL Developer 3.2.10.09.57. Once we get this resolved, I’m hoping to be able to write queries and pull data from these various data sources using only SQL Developer. In my experience, there were slight differences in SQL code depending on database (Oracle SQL vs. MS Access SQL for example). Will the same SQL code work regardless of the database as long as I’m using SQL Developer as my GUI? Sorry if you went over this before and I missed it.
Thanks, Mike.
The Query Builder is disabled for SQL Server and Teradata connections – the requirement to have a proper Query Builder for Oracle was costly enough that having it support other database platforms was just too much.
That being said, you SHOULD be able to write reasonably universal code if you adhere to ANSI SQL. You’ll definitely run into differences and peculiarities. We’re looking to extend Oracle’s SQL capabilities so that less SQL rewrites are required when moving applications from SQL Server, Sybase, etc…
BUT – I think you might enjoy our translator. Have you seen this?
http://www.thatjeffsmith.com/archive/2011/12/sql-developer-give-me-a-google-translator-for-sql/
The translator sounds like a possible solution to my issue. Thanks.
I never thought i would use QB, but I will now – very cool
Wow, thanks Boris. I’m happy to hear that I’ve converted at least one person 🙂 Thanks for sharing!
Pingback: SQLDev Tip and Trick: Drag and Drop to Worksheet