Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ANSI SQL Syntax - What belongs to join-clause and what to where-clause

931425Aug 13 2012 — edited Aug 13 2012
Hello,

we currently have a discussion about the ANSI SQL Syntax where we do not agree what belongs to the join clause and what belongs to the where clause in an ANSI Sytnax SQL Query.

Lets say there is a query like this:

+SELECT *+
FROM employees emp, departments dept
WHERE emp.dept_country = dept.dept_country
AND emp.dept_name = dept.dept_name
AND dept.dept_type = 'HQ'
AND emp.emp_lastname = 'Smith'

Primary key of the departments table is on the columns dept_country, dept_name and dept_type. We have a Oracle database 10g.
Now I have rewritten the query to Ansi Syntax:

+SELECT *+
FROM employees emp
JOIN departments dept
ON emp.dept_country = dept.dept_country AND emp.dept_name = dept.dept_name
WHERE dept.dept_type = 'HQ'
AND emp.emp_lastname = 'Smith'

Another developer says that this is not completely correct, every filter on a column that belongs to the primary-key of the joined table has to be in the join clause, like this:

+SELECT *+
FROM employees emp
JOIN departments dept
+ON emp.dept_country = dept.dept_country AND emp.dept_name = dept.dept_name AND dept.dept_type = 'HQ'
WHERE emp.emp_lastname = 'Smith'

Can somebody tell me which on is correct?
Is there any definition for that? I couldn't find it in the Oracle Database definition.
I just found out the names of the ANSI documents here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/ap_standard_sql001.htm#i11939
I had a look at the ANSI webstore but there you have to buy the PDF files. In my case thats exaggerated because both of the Queries work and i am just interessted if there is one correct way.

Thank you in advance
Marco
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 10 2012
Added on Aug 13 2012
7 comments
1,080 views