SQL Developer is a free tool from Oracle which is great for database querying and extraction and the best thing is that it doesn’t just support Oracle it also supports Access, MySQL and SQL Server. Another hidden feature for all the novice SQL programmers is the Query Builder (similar to Access and SQL Server), so I thought I would do a quick post to go over the features of Query Builder.

Where is it?

Firstly Open up a Blank SQL Worksheet, to do this click on the icon found on the toolbar at the top, then select a connection you would like to build your query on. In the example below I’m going to build my query on a connection called EARTH_ORCL.

You should now see a new tab appear which will be labelled the name of your connection.

Next right click in the area where you would type your SQL query and select the Query option.

That’s it! You should now be able to see the Query Builder window.

Layout

Below is an overlay of the layout of the query builder you might need to click on the image to open up a larger version.

Building a Query

The query can be built in two different sections:

  • SELECT Columns Tab : Where the SELECT part of your statement is built
  • Create Where Clause Tab : Where the WHERE part of your statement is built

Lets start with the first tab.

Select Columns

You can do more than just create the SELECT part of your SQL statement, in this area you are also able to perform joins! So lets get started…

Drag and drop a table from the left hand side pane to the right hand side area, the table should now appear in the right hand side area along with the columns available.

To select which colums you want in your SELECT statement click on the check boxes next to the columns or click on the check box available at the top to select all columns (or *). In my example I have only chosen four columns.

Now, I need to join this table with another table, to do this drag and drop another table onto the same area. Then drag and drop a column onto the other column which you would like to perform the join on.

Once you have dropped the link, you should now see a join has been completed, I’m going to select only one field from the second table.

You can change the properties of the join by right clicking on it and selecting one of the options available.

Now we should be able to see what the SELECT part of our SQL statement looks like, click on the Show SQL tab. It doesn’t look pretty but it will do for now, lets move onto the WHERE statement!

Create Where Clause

Click on the Create Where Clause and you should notice a blank WHERE clause already setup, there are three parts to this blank WHERE clause:

In my example I will first select the P_CODE column from the AR_POSTCODE_GEO table.

Then from the operator drop down box I will select contains.

To finish of this WHERE clause I will enter my condition.

There you have it, I have just created a WHERE clause for my SQL statement, clicking on the Show SQL tab will represent what we have built so far:

Adding more Conditions

You can add more conditions by right clicking in the Create Where Clause tab’s area and choosing the Add Condition option.

After adding a second condition, you will notice that a conditional operator drop down box will appear on the left hand side.

There are four different conidtional options available :

Nesting Conditions

The great feature of the WHERE clause is that you can nest them, right click on the operational condition you have set up and a menu will pop up asking to insert a condition.

As you can see, I have inserted an OR condition and it has nested the AND statement.

For this example I’m going to keep it simple and use only one WHERE condition.

Viewing Results

To see what the final output of the SQL statement you have built is, click on the View Results tab and then click on the Green play button.

The query which was just built should display as a data set :

You can also set the query to refresh automatically by using the Refresh: drop down box :

After you happy with the query you have built and the resulting data set which is produced, click on the Apply button, this will bring back to the SQL worksheet for the database we were working on and have the SQL statement laid out!

Looks ugly? To format it hit CTRL + B and it should be formatted perfectly now.

IMPORTANT NOTE!
Once you create a query using the query builder you can not use query builder to edit it, you will have to start from the beginning, so make sure you data set is correct before clicking on the Apply button!

So that’s how you can use the query builder within Oracle’s SQL Developer, did I forget to mention that it’s free? :)

Click here to download SQL Developer

Enjoy!