Friday, September 10, 2010

Fast writing of Queries with Joins in SQL Developer

When you create a report in APEX and build the source of that SQL query with the wizards you get these annoying quotes (") around the column and table names. It also takes time to do it with the wizard, time I don't have.

I tend to write my queries in SQL Developer so I can immediately fine-tune the query so it's exactly what I need. Writing your queries in a tool like SQL Developer has many more advantages, as you can look back in the history of your statements, you can immediately do an Explain Plan and format the code but even more immortantly it's very fast to write statements.

Let's say you need to write a query that joins four tables, you can just write it manually... Or your could just select the tables from the tree and drag them on your SQL Worksheet. SQL Developer will ask if it needs to join the tables for you and write the statement. It uses aliases for the tables, which is also something I like better than using the full table names in front of the columns...

Remark: you need foreign keys in place so the joins are written correctly.

As I saw many people didn't seem to know about that, I thought to quickly write something as it's definitely a time saver.

1 comment:

FranzSoft said...

I always use Allround Automations "PL/SQL Developer". It is making a select framework with a drag & drop action in a Query Builder. It suggesting possible joins.
During customizing the query, I clean-up the query-text by PL/SQL Beautifier (Flemish: Kuiser?). So I produce queries very fast and neat.