Oracle Query Builder Easy Guide: How to Write Queries Faster

“Oracle query builder? You mean to visually craft my query so that I click more and type less?”, asked Dan.

Dan is an Oracle pro. He attacks SQL queries head-on. He crafts his joins, subqueries, and anything SQL by typing.

“Besides, code autocompletion and code suggestions are there to help. And I know my craft.”, he explains.

There are pros like Dan who let an Oracle query builder just sit there unused.

But work today demands fast results. This tells us whether pros or otherwise to use all the productivity tools at our disposal.

So, before we proceed…

Let’s Map Our Oracle Query Builder Journey First

This is a guide with you (a pro) in mind. But the steps later are equally useful for newbies or novices too.

So, here’s how we go through this journey:

• An Oracle Query Builder is a Complement, not a Replacement. Let’s dispel the myth together that we are trying to replace good old typing with visual query building.
• This Oracle Query Builder is Your Bet for Faster Query Building. You’ll learn about dbForge Studio for Oracle and its visual query-building powers.
• How to Visually Craft Simple to Complex Oracle Queries. Learn using the visual Query Builder in dbForge Studio for Oracle. This is like a stairway of different steps from simple to complex queries.

Let’s start.

An Oracle Query Builder is a Complement, not a Replacement

Some pros worry about precision and control. They want to craft their queries without code generators interfering.

“I worry about query performance. Code generators like an Oracle query builder may not cut it.”, said Dan.

Performance is a real concern. But is it true that Oracle query builders get in the way?

Here’s the truth: Visual query building complements SQL query writing, not replace it.

Let me explain.

Oracle query builders today generate SQL code with joins, subqueries, GROUP BY, and much more. You use your mouse instead of typing columns, table names, join types, etc.

So, that means you drag the tables on a canvas. If there are foreign keys, it will use them to generate joins for you. But you can still replace the type of JOIN from INNER to LEFT, if that’s what you need. Then, add a WHERE, GROUP BY, HAVING, and other clauses. You do most of them by clicking.

If you feel it’s still incomplete or doesn’t suit your level of control and precision, you can switch to typing. From there, code autocompletion, suggestions, and other code productivity hacks will help you.

An Oracle query builder can start the foundation of your query. And the more intricate parts are up to you to type.

Did you lose control and precision? Not at all.

So, visual query building makes it faster to write SQL code.

But what Oracle GUI tool can make this happen?

This Oracle Query Builder is Your Bet for Faster Query Building

My best bet is dbForge Studio for Oracle. It can be yours too.

Devart made this outstanding GUI tool as an Oracle productivity booster. It is a jack-of-all-trades for anything Oracle. Coding is a breeze with a smart PL/SQL coding assistant. Talk about automatic code completion, code snippets, and much more. It also has a Database Designer, a Query Profiler, an SQL debugger, and all the works.

But there’s more.

Its visual query-building abilities are the easiest to get used to. Here are some of them:

• Visually make SELECT statements like a walk in the park.
• See your query’s tables and their relationships in a beautiful diagram.
• Automatic INNER JOIN if your tables have foreign keys. Then, change it to LEFT, RIGHT, or FULL with mouse clicks.
• Provided full support to subqueries and navigated from the main query to the subqueries.
• A neat Expression Editor for your WHERE, HAVING, and JOIN clauses with Oracle functions support.
• An easy switch between visual and manual query writing so you can have more control on the resulting query.
• A Document Outline to see your query in a neat tree structure. This is useful for complex queries with several database objects and subqueries.
• Edit the data in query results, export them, or send them to a report designer.
• and much more.

Looks nice on paper. But check out the steps later to see its true power for yourself.

Note that my version of dbForge Studio for Oracle is 4.6.36. This version does not include visually creating INSERT, UPDATE, and DELETE statements, unlike dbForge Studio for SQL Server and MySQL. But I’m sure it’s in the development pipeline.

You need to download and install dbForge Studio for Oracle. If you’ve done that already, let’s proceed.

How to Visually Craft Simple to Complex Oracle Queries

The number of steps differ depending on the complexity of your query. Let’s take this one step at a time by starting with simple queries.

But before that, we’re going to use this sales database:

We have 5 tables: CUSTOMER, STORE, PRODUCT, SALES_HEADER, and SALES_DETAIL.

Let’s roll.

How to Craft Simple Queries in 2 to 3 Steps

dbForge Studio for Oracle’s visual Query Builder makes it easy to create simple SELECT statements. You can ask your 5-year-old kid to drag and click for you. That’s how easy it is.

Run your new dbForge Studio for Oracle and sign in to an Oracle server. Then, follow the steps below:

STEP 1: Drag the Customer Table from the Database Explorer into the Query Builder

Let’s use the CUSTOMER table. Click New Query from the toolbar. Then, from the Database Explorer, drag this table into the Query Builder as seen below:

The Query Builder canvas is white with grids. You can position the table and snap it to these lines to your liking.

STEP 2: Mark the Columns You Want to Appear in the Query

Again, this is easy. You won’t need your keyboard.

There are checkboxes in each table column. Select the columns you want to appear in the query. Check it out below:

Selected columns appear in the Selection tab. From here, you can add an alias, an aggregate function (like SUM or COUNT), and more for each column.

If this is what you need for now, you can switch to the SQL code window by clicking the Text button below the Selection tab. See it below and check the generated SQL code:

If this is the only thing you need, the simple query steps end here. But you can do more like the one in the next step.

STEP 3: Add a WHERE Clause

Queries are rarely without filters because you only get what you need.

So, let’s filter the results by adding a WHERE clause. First, switch back to the visual Query Builder, and click the WHERE tab. You will see a similar screen below:

The WHERE tab is the place to form the expression for the WHERE condition.

Expressions have a left portion where you usually put one of the columns. Then, there’s an operator like equal (=), greater than (>), BETWEEN, and more. Finally, there’s a right portion where you put values or another expression you want to qualify against the left portion.

Press the Insert key from your keyboard or click the little green plus (+) button to add an expression. After doing that, you will see a blank expression ready for you to configure. See below:

Then, you choose what goes to the left side of the expression by clicking <enter a value>. See below:

For our purpose, we will choose the CUSTOMER_ID column as the left side of the expression.

Then, you need to change the operator. It defaults to the equal (=) operator. Click that. Then, choose BETWEEN. Check it out below:

Then, for the right side of the expression, we will enter 2 values needed by BETWEEN. This time, you need a keyboard to type the values. See below for the first value:

And the second value:

Finally, check the following edited SQL code below:

At last, you can run the query by clicking Execute from the toolbar. See the results on my end below:

How to Craft Queries with Joins in 4 Steps

Let’s take this to the next level by adding some joins.

The dbForge Studio family of products, including dbForge Studio for Oracle, is known for superb code autocompletion and suggestions. But the ninja moves extend to the Query Builder as well.

Let’s begin.

STEP 1: Drag the Tables to the Query Builder

This is the same as earlier, but you must drag three tables instead of one. We will use the same Oracle database as before. So, click New Query from the toolbar. Then, drag SALES_HEADER, CUSTOMER, and STORE tables into the Query Builder.

It should be the same as the following:

STEP 2: Mark the Columns You Want to Appear in the Query

Since we want to see the transactions of a particular customer, the date, and the store, let’s choose the columns as seen below:

Simply mark the check boxes of the columns you want.

STEP 3: Add the Joins

The SALES_HEADER table has foreign keys for the store and customer IDs. That’s why the images earlier already contain arrows to the CUSTOMER and STORE tables. It created INNER JOINs for you.

Nifty, isn’t it?

Click the Joins tab to see them.

It should be the same as the illustration below:

That was too easy if you intend to use an INNER JOIN.

But if your tables don’t have foreign keys, you can still add a join in 2 ways:

• Click the CUSTOMER_ID of the SALES_HEADER table and drag it to the CUSTOMER_ID of the CUSTOMER table. An arrow will appear just like the above. Do the same for STORE_ID and the STORE table.
• From the Joins tab, press the Insert key from the keyboard or click the little plus button. Then, enter the related tables and the related columns. Do that for the CUSTOMER and STORE tables. You should get the same result as the above.

See how to manually join tables below:

Simply click <enter table name> on both sides. You will select SALES_HEADER and CUSTOMER.

Then, manually relate columns as seen below:

Simply click <enter column name> for both sides. You will choose the CUSTOMER_ID of the SALES_HEADER and CUSTOMER tables.

For the next join, click the little plus button from the top. Then, select SALES_HEADER and STORE tables. Then, relate the STORE_ID of these 2 tables.

If you want to change the join type, click Inner Join. Then, select another join type. Check out the screenshot below:

For less clicking, your tables should have foreign keys.

STEP 4: Add a WHERE Clause

We intend to filter the result to one customer only (CUSTOMER_ID = 56). So, click the WHERE tab. Then, add the same WHERE clause as below:

Finally, switch to Text to see the generated SQL code. See below:

Run the query by clicking Execute from the toolbar. See the query results below:

How to Craft Queries with a Subquery in 3 Steps

For this exercise, we wanted to show all customers without any sales transactions.

We will use a subquery of all customers with sales transactions using the SALES_HEADER table. Then, we will filter the results from the CUSTOMER table with CUSTOMER_ID , which is not in the SALES_HEADER.

STEP 1: Drag the CUSTOMER Table into the Query Builder

You should have the same result for this step as the one below:

STEP 2: Choose the Columns You Want to Appear in the Query

We want the customer’s name, email, and mobile number. So, it should be the same as below:

STEP 3: Add the WHERE Clause with Subquery

The WHERE clause should filter the CUSTOMER.CUSTOMER_ID is not in the customer IDs in SALES_HEADER.

First, click the WHERE tab and choose the CUSTOMER_ID for the left side of the expression. See below:

Then, click the equal (=) operator and select NOT IN. See it below:

Creating the Subquery

Now, the condition’s right side should result from a subquery. So, click <enter a value>. Then, click the Create subquery button. See it below:

A new tab will appear with the label Unnamed Query. The Document Outline window will also appear.

From here, drag the SALES_HEADER table into the Query Builder under the Unnamed Query. Then, choose the CUSTOMER_ID and click Unique records. See the result of this below:

Go back to the main query by clicking the Root Query. You should be in the WHERE tab. If not, click it, and you will see the new subquery in the WHERE clause. Check it out below:

Then, switch to the Text window to see the generated SQL code:

From here, you can optionally add an ORDER BY. Switch back to the Query Builder, then click the ORDER BY tab. Choose the CUSTOMER_NAME column as the sort order.

Finally, here’s the query result on my end:

Note that the same results can be achieved using a LEFT JOIN instead of a subquery. You can use the Query Profiler of dbForge Studio for Oracle to see which performs better.

But there’s more to subqueries in the Query Builder.

Subqueries can be added as part of the SELECT column list by clicking the Create subquery in another row in the Selection tab. See below:

The column in the subquery can result from an aggregate function like SUM. See below:

And of course, you can switch to typing anytime you want to add more complex query structures.

Takeaways

Dan’s jaw dropped as a coworker demos the query magic in dbForge Studio for Oracle.

He realizes the speed it can give him to craft his queries. He will be on top of his game on every project, and his team can enjoy it, too.

And the result for all of them?

They can leave the office sooner, have a good time in a nice bar, or relax in the comfort of their homes.

And his boss?

Nothing but smiles on every KPI they meet.

So, remember: this Oracle query builder is a complement, not a replacement to typing SQL statements. It can do joins, subqueries, ORDER BY, and more.

Try it out today by downloading a copy from Devart’s official website.