SQL Developer Data Modeler Quick Tip: Use Oracle JOIN Syntax or ANSI

New for version 3.1, currently available as a EA download, when defining the queries used to populate views, the modeler can generate JOINs using either Oracle’s or the standard ANSI compliant syntax.

Let’s step back a second.

When working with a VIEW defined in a relational model, the modeler has twp choices for defining the driving SQL statement that is used to populate the contents of the view. They can code it by hand – ideal when it’s a pretty simple SELECT * FROM with a few WHERE clauses, OR they can use the visual query builder and draw it up.

When going with the visual design method, JOIN clauses by default will use Oracle’s database syntax. We’re Oracle and we like our syntax. But, sometimes folks have silly (mostly a joke, mostly) rules in place that require as generic SQL syntax as possible. Maybe they find it easier to read, or maybe they think it will make a migration easier down the road. That’s not really important, however it is important to realize that the Modeler supports both methodologies.

You can access the visual query builder support directly from the View editor –

For this example I have done a reverse-engineer of the SCOTT, HR, and SH schemas. One of the views here is HR.EMP_DETAILS_VIEW. Here’s the default SQL statement for that view:

[sql]
SELECT e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.COUNTRY_ID,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.JOB_TITLE,
l.CITY,
l.STATE_PROVINCE,
c.COUNTRY_NAME,
r.REGION_NAME
FROM employees e,
departments d,
JOBS j,
LOCATIONS l,
COUNTRIES c,
REGIONS r
WHERE e.department_id = d.department_id
AND d.location_id = l.LOCATION_ID
AND l.COUNTRY_ID = c.COUNTRY_ID
AND c.REGION_ID = r.REGION_ID
AND j.JOB_ID = e.job_id
[/sql]

With the click of a button..

Automagically translate Oracle joins to ANSI or vice-versa

With this ‘magic’ button, I’ve saved myself probably a few minutes. And that’s what I’m after – a tool that can save me time.

And here’s our new code
[sql]
SELECT e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.COUNTRY_ID,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.JOB_TITLE,
l.CITY,
l.STATE_PROVINCE,
c.COUNTRY_NAME,
r.REGION_NAME
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN JOBS j
ON j.JOB_ID = e.job_id
INNER JOIN LOCATIONS l
ON d.location_id = l.LOCATION_ID
INNER JOIN COUNTRIES c
ON l.COUNTRY_ID = c.COUNTRY_ID
INNER JOIN REGIONS r
ON c.REGION_ID = r.REGION_ID
[/sql]

Yes, it’s more VERBOSE, but some folks just prefer this method. And now you can choose a modeling tool that doesn’t make you choose just one implementation of SQL. And yes, you can go in the OTHER direction.

Bonus Tip: Validate your Query

Once you’ve defined your query, SQL Developer Data Modeler will allow you to test the query against a live database before saving it to the model. Being able to see the data that will come back when someone queries the view will give you a bit of a confidence boost, especially if you don’t trust these new-fangled ANSI JOIN statements. You can also inspect the Execution Plans. I’ve heard rumors that using ANSI vs Oracle join syntax can impact the plans the CBO optimizer will generate, but you should test this for yourself. Remember, I’m just a simple hacker at heart.