You want to write a query. You want to use a subquery in that query. You don’t want to type so much.

What to do?

Enter the Query Builder.

It’s been part of your worksheet for years.

Click on it.

Drag a table into it.

click on 'department_id' in the checklist - we're going to use that in our where clause predicate
click on ‘department_id’ in the checklist – we’re going to use that in our where clause predicate

But I don’t want to see the department_id in the output of my query, so uncheck it in the ‘Output’ column down below.

Now right click in its corresponding Criteria box.

This will give us another query builder panel to draw the subquery in.
This will give us another query builder panel to draw the subquery in.

Now look ‘up’ – you’ll see there’s a new tab/panel.

CLICK ON THIS
CLICK ON THIS

Now we have a new query builder of sorts. I’m going to drag over the DEPARTMENTS table.

I’m only going to bring back the DEPARTMENT_ID – because that’s why my main query expects to fulfill the predicate clause.

And now I’ll also add the DEPARTMENT_NAME, but unchecking the ‘Output’ column again, and add a WHERE clause there. I I only want to return departments that don’t have a name with the text ‘SALES’ in them – no offense folks!

We're almost done!
We’re almost done!

Let’s go look at our generated code and run the query.

Ta-da.
Ta-da.

The Query Builder is also useful for generating JOINS

Go read/watch this.

thatjeffsmith
Author

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

5 Comments

  1. Andy Block Reply

    Hey Jeff, is there a way to run the subquery in the Query builder, as opposed to having to run the entire query? I often find myself in situations with several levels of nesting, and it’s a little tricky to find out where the error is sometimes.

    • thatjeffsmith

      No…but you could switch out of the QB to the worksheet and highlight what you want to run.

  2. John Garmon Reply

    Jeff. I don’t know what to say. Did you write this? It doesn’t seem to work the way it is written?

    • John Garmon

      I’m sorry. It has been a long day. Let me try it again in the morning.
      john3

Write A Comment