Stumbling around on StackOverflow this morning, I came across this person’s HOMEWORK question, and the answer chided them on not using ANSI style JOINs.

I’m not going to post the actual SQL here, but if you follow the previous link, it’s there for you to grab and have a go.

I hope they get an A+

Something I like to do is take REAL code off the innerwebs and use it to test our parser, formatter, and even the Query Builder.

So let’s look at how the Query Builder handles this query:

And YES, I formatted it first. But anyways…it gives us this:

This is pretty boring, it’s just the SELECT * FROM the inline view bit.

The fun or main part of the query can be found here:

Cool.

How do I get Oracle Style Joins?

Let’s switch that from the SO answer with ANSI joins over to a traditional Oracle-looking query. So right-click:

And now toggle back to the Worksheet:

Cool!

A Few Parting Notes

If you were building this query from scratch, when you go to build the subquery, right-click in the diagram space:

Then toggle over to ‘Query1’ and ‘draw’ your inline view there
  • I’m not debating ANSI or Oracle JOINs here
  • Yes, it goes ‘both ways’ – you can convert the join from your WHERE to your FROM clause if you’d like folks on SO to not yell at you
  • You can read about inline views in general here (Oracle Docs)
  • Make sure you’re on version 18.2 or higher of SQLDev to access the JOIN switcher-feature

We fixed a HUGE performance issue with the Query Builder in the last drop of SQL Developer. IF you’re running an old copy and you want to give the query builder a play, upgrade FIRST.

Looking for a more basic overview?

Here’s an 8 minute video to get you started working with SQL in either the worksheet or the Query Builder.

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.

Write A Comment