| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Parentheses and joins
Content-Type: text/plain; charset="us-ascii"
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
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
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
scott.emp m join
(scott.emp e join scott.dept d on e.deptno=d.deptno)
on m.mgr = e.empno
/
Jonathan Gennick <jonathan_at_gennick.com>
Sent by: ml-errors_at_fatcity.com
12/10/2003 10:54 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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:jonathan@gennick.com
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 Oracle-article-request_at_gennick.com 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: jonathan_at_gennick.com 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: ListGuru_at_fatcity.com (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). --=_alternative 006F6EF188256DF8_= Content-Type: text/html; charset="us-ascii"Received on Wed Dec 10 2003 - 14:19:24 CST
<br><font size=2 face="sans-serif">Jonathan,</font>
<br>
<br><font size=2 face="sans-serif">I didn't realize that parentheses would affect to optimizer to change the join order.</font>
<br>
<br><font size=2 face="sans-serif">A little experimentation shows that it can indeed be done.</font>
<br>
<br><font size=2 face="sans-serif">For anyone that wants to see it, the joins are below.</font>
<br>
<br><font size=2 face="sans-serif">I learn something every day around here.</font>
<br>
<br><font size=2 face="sans-serif">Jared</font>
<br>
<br><font size=2 face="Courier">select</font>
<br><font size=2 face="Courier"> e.ename</font>
<br><font size=2 face="Courier"> , e.empno</font>
<br><font size=2 face="Courier"> , d.dname</font>
<br><font size=2 face="Courier"> , m.ename</font>
<br><font size=2 face="Courier">from</font>
<br><font size=2 face="Courier"> scott.emp e</font>
<br><font size=2 face="Courier"> join scott.dept d on d.deptno=e.deptno</font>
<br><font size=2 face="Courier"> join scott.emp m on m.empno=e.mgr</font>
<br><font size=2 face="Courier">/</font>
<br>
<br>
<br><font size=2 face="Courier">select</font>
<br><font size=2 face="Courier"> e.ename</font>
<br><font size=2 face="Courier"> , e.empno</font>
<br><font size=2 face="Courier"> , d.dname</font>
<br><font size=2 face="Courier"> , m.ename</font>
<br><font size=2 face="Courier">from</font>
<br><font size=2 face="Courier"> scott.dept d join</font>
<br><font size=2 face="Courier"> (scott.emp e join scott.emp m on m.empno=e.mgr)</font>
<br><font size=2 face="Courier"> on d.deptno = e.deptno</font>
<br><font size=2 face="Courier">/</font>
<br>
<br>
<br><font size=2 face="Courier">select</font>
<br><font size=2 face="Courier"> e.ename</font>
<br><font size=2 face="Courier"> , e.empno</font>
<br><font size=2 face="Courier"> , d.dname</font>
<br><font size=2 face="Courier"> , m.ename</font>
<br><font size=2 face="Courier">from</font>
<br><font size=2 face="Courier"> scott.emp m join</font>
<br><font size=2 face="Courier"> (scott.emp e join scott.dept d on e.deptno=d.deptno)</font>
<br><font size=2 face="Courier"> on m.mgr = e.empno</font>
<br><font size=2 face="Courier">/<br>
</font>
<br>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td>
<td><font size=1 face="sans-serif"><b>Jonathan Gennick <jonathan_at_gennick.com></b></font>
<br><font size=1 face="sans-serif">Sent by: ml-errors_at_fatcity.com</font>
<p><font size=1 face="sans-serif"> 12/10/2003 10:54 AM</font>
<br><font size=2 face="sans-serif"> </font><font size=1 face="sans-serif">Please respond to ORACLE-L</font>
<br>
<td><font size=1 face="Arial"> </font>
<br><font size=1 face="sans-serif"> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com></font>
<br><font size=1 face="sans-serif"> cc: </font>
<br><font size=1 face="sans-serif"> Subject: Parentheses and joins</font></table>
<br>
<br>
<br><font size=2 face="Courier New">I looking for a good, simple example of when parentheses<br>
matter when writing a join query using the new, SQL92 join<br> syntax. For example, I could write:<br>
<br>
SELECT *<br> FROM county y JOIN city c<br> ON y.county_id = c.county_id<br> JOIN attraction a<br> ON c.city_id = a.city_id;<br>
<br>
and I could use parens to clarify the join order:<br>
<br>
SELECT *<br> FROM (county y JOIN city c<br> ON y.county_id = c.county_id)<br> JOIN attraction a<br> ON c.city_id = a.city_id;<br>
<br>
I could even force a different join order:<br>
<br>
SELECT *<br> FROM county y JOIN (city c<br> JOIN attraction a<br> ON c.city_id = a.city_id)<br> ON y.county_id = c.county_id;<br>
<br>
I'm struggling to come up with a good example of when you<br> might *need* to use parens. I'm not having a really good day<br> today. In fact, I'm having a really, really bad day, so<br> maybe I'm not thinking too clearly, but so far I'm unable to<br> come up with a good example to demonstrate the necessity of<br> parentheses. Can anyone help me out with this?<br>
<br>
Best regards,<br>
<br>
Jonathan Gennick --- Brighten the corner where you are<br> http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com<br>
<br>
Join the Oracle-article list and receive one<br> article on Oracle technologies per month by <br> email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, <br> or send email to Oracle-article-request_at_gennick.com and <br> include the word "subscribe" in either the subject or body.<br>
<br>
-- <br> Please see the official ORACLE-L FAQ: http://www.orafaq.net<br> -- <br> Author: Jonathan Gennick<br> INET: jonathan_at_gennick.com<br>
<br>
Fat City Network Services -- 858-538-5051 http://www.fatcity.com<br> San Diego, California -- Mailing list and web hosting services<br> ---------------------------------------------------------------------<br> To REMOVE yourself from this mailing list, send an E-Mail message<br> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in<br> the message BODY, include a line containing: UNSUB ORACLE-L<br> (or the name of mailing list you want to be removed from). You may<br> also send the HELP command for other information (like subscribing).<br>
</font>
<br>
<br>
--=_alternative 006F6EF188256DF8_=-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Jared.Still_at_radisys.com 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: ListGuru_at_fatcity.com (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).
 |
 |