How to Build a SubQuery with SQL Developer’s Query Builder

thatjeffsmith SQL Developer 5 Comments

Tell Others About This Story:

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.

Tell Others About This Story:

Comments 5

  1. 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.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *