Subquery and JOIN - Revision history

Andr3w at 15:17, 6 January 2013

2013-01-06T15:17:55Z

2012-08-02T14:13:29Z

2012-08-02T14:13:08Z

2012-08-02T14:12:24Z

2012-08-02T13:12:28Z

2012-08-02T13:12:16Z

2012-08-02T09:52:22Z

2012-08-02T09:52:07Z

2012-07-30T13:34:48Z

2012-07-30T13:33:54Z

@@ Line 1: / Line 1: @@
-<p>Here you are shown how to convert subqueries into JOIN functions this would be done
+Here you are shown how to convert subqueries into JOIN functions this would be done
-as using subqueries that contain no aggregate functions unnecessarily</p>
+as using subqueries that contain no aggregate functions unnecessarily
 causes the response speed of the query to slow down.
 <div class='ht'>

@@ Line 50: / Line 50: @@
 <div class="ecomm e-mysql" style="display: none"><code>JOIN ON</code> functions would also work with:
 <pre>SELECT payment
-   FROM salary JOIN ranks ON (salary.rank = ranks.rank)
+   FROM salary JOIN ranks
    JOIN jobs ON (ranks.title = jobs.title)
    WHERE jobs.employee = 'Andrew Cumming'</pre></div>
 </div>
 {{Hacks Ref}}

@@ Line 49: / Line 49: @@
      AND jobs.employee = 'Andrew Cumming'</source>
 <div class="ecomm e-mysql" style="display: none"><code>JOIN ON</code> functions would also work with:
-SELECT payment
+<pre>SELECT payment
    FROM salary JOIN ranks ON (salary.rank = ranks.rank)
    JOIN jobs ON (ranks.title = jobs.title)
-   WHERE jobs.employee = 'Andrew Cumming'</div>
+   WHERE jobs.employee = 'Andrew Cumming'</pre></div>
 </div>
 {{Hacks Ref}}

@@ Line 29: / Line 29: @@
 <div>
   SELECT payment FROM salary WHERE rank =
- (SELECT rank FROM ranks WHERE title =
+  (SELECT rank FROM ranks WHERE title =
- (SELECT title FROM jobs
+  (SELECT title FROM jobs
-  WHERE employee = 'Andrew Cumming'))
+    WHERE employee = 'Andrew Cumming'))
 This subquery would be more efficient if it was changed to a <code>JOIN</code> as there are no
 aggregate functions in the queries.
@@ Line 42: / Line 42: @@
 </ul>
 </div>
-<source lang='sql' class='def'>SELECT payment FROM salary, ranks, jobs
+<source lang='sql' class='def'>
-WHERE salary.rank = ranks.rank
-AND ranks.title = jobs.title
-AND jobs.employee = 'Andrew Cumming'</source>
-<div class="ecomm e-mysql" style="display: none"><code>JOIN ON</code> functions would also work with
 SELECT payment
-FROM salary JOIN ranks ON (salary.rank = ranks.rank)
+  FROM salary, ranks, jobs
-JOIN jobs ON (ranks.title = jobs.title)
+  WHERE salary.rank = ranks.rank
-WHERE jobs.employee = 'Andrew Cumming'</div>
+    AND ranks.title = jobs.title
 </div>
 {{Hacks Ref}}

@@ Line 1: / Line 1: @@
 <p>Here you are shown how to convert subqueries into JOIN functions this would be done
-as using subqueries that contain no aggregate functions unnecessarily</p> causes the
+as using subqueries that contain no aggregate functions unnecessarily</p>
-response speed of the query to slow down.
+causes the response speed of the query to slow down.
 <div class='ht'>
 <div class=params>schema:scott</div>

@@ Line 34: / Line 34: @@
 <ul>Use the following steps to change a subquery into a <code>JOIN</code>
   <li>Mark all columns with the table name they came from</li>
-  <li>If you use the same table in two different FROM clauses, use aliases</li>
+  <li>If you use the same table in two different <code>FROM</code> clauses, use aliases</li>
-  <li>Move all FROM statements together to form a single FROM </li>
+  <li>Move all <code>FROM</code> statements together to form a single <code>FROM</code> </li>
   <li>Delete all occurrences of (<code>SELECT</code> </li>
   <li>Substitute <code>WHERE</code> for AND after the first occurence of <code>WHERE</code></li>
@@ Line 44: / Line 44: @@
 AND ranks.title = jobs.title
 AND jobs.employee = 'Andrew Cumming'</source>
-<div class="ecomm e-mysql" style="display: none">JOIN ON functions would also work with
+<div class="ecomm e-mysql" style="display: none"><code>JOIN ON</code> functions would also work with
 SELECT payment
 FROM salary JOIN ranks ON (salary.rank = ranks.rank)