Jonathan,
I didn't realize that parentheses would affect to optimizer to change the join order.
A little experimentation shows that it can indeed be done.
For anyone that wants to see it, the joins are below.
I learn something every day around here.
Jared
select
e.ename
, e.empno
, d.dname
, m.ename
from
scott.emp e
join scott.dept d on d.deptno=e.deptno
join scott.emp m on m.empno=e.mgr
/
select
e.ename
, e.empno
, d.dname
, m.ename
from
scott.dept d join
(scott.emp e join scott.emp m on m.empno=e.mgr)
on d.deptno = e.deptno
/
select
e.ename
, e.empno
, d.dname
, m.ename
from
scott.emp m join
(scott.emp e join scott.dept d on e.deptno=d.deptno)
on m.mgr = e.empno
/
| Jonathan Gennick <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 12/10/2003 10:54 AM
|
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Parentheses and joins |
I looking for a good, simple example of when parentheses
matter when writing a join query using the new, SQL92 join
syntax. For example, I could write:
SELECT *
FROM county y JOIN city c
ON y.county_id = c.county_id
JOIN attraction a
ON c.city_id = a.city_id;
and I could use parens to clarify the join order:
SELECT *
FROM (county y JOIN city c
ON y.county_id = c.county_id)
JOIN attraction a
ON c.city_id = a.city_id;
I could even force a different join order:
SELECT *
FROM county y JOIN (city c
JOIN attraction a
ON c.city_id = a.city_id)
ON y.county_id = c.county_id;
I'm struggling to come up with a good example of when you
might *need* to use parens. I'm not having a really good day
today. In fact, I'm having a really, really bad day, so
maybe I'm not thinking too clearly, but so far I'm unable to
come up with a good example to demonstrate the necessity of
parentheses. Can anyone help me out with this?
Best regards,
Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article,
or send email to [EMAIL PROTECTED] and
include the word "subscribe" in either the subject or body.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Gennick
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
