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.
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:
The fun or main part of the query can be found here:
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:
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:
- 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.