Difference between revisions of "Subquery and JOIN"

From SQLZOO
Jump to: navigation, search
Line 1: Line 1:
Here you are shown how to convert subqueries into JOIN functions this would be done
+
SELECT cfu.* , ccd.*,count(*) as countt FROM (Select * from crm_follow_up
as using subqueries that contain no aggregate functions unnecessarily
+
ORDER BY follow_id DESC) as cfu left join crm_call_data as ccd
causes the response speed of the query to slow down.
+
  ON(cfu.sr_no=ccd.sr_no) WHERE ccd.assignto='neha' and ccd.data_mode=0
<div class='ht'>
+
GROUP BY ccd.name ORDER BY cfu.follow_id DESC
<div class=params>schema:scott</div>
+
<source lang=sql class='tidy'>DROP TABLE jobs;
+
DROP TABLE ranks;
+
DROP TABLE salary;</source>
+
<source lang=sql class='setup'> CREATE TABLE jobs(
+
  employee VARCHAR(40),
+
  title VARCHAR(40));
+
INSERT INTO jobs VALUES ('Gordon Russell','Lecturer');
+
INSERT INTO jobs VALUES ('Andrew Cumming','Teaching fellow');
+
INSERT INTO jobs VALUES ('Jim Smith','Technician');
+
CREATE TABLE ranks (
+
  title VARCHAR(40),
+
  rank VARCHAR(40));
+
INSERT INTO ranks VALUES ('Lecturer','LECT1');
+
INSERT INTO ranks VALUES ('Teaching fellow','LECT2');
+
INSERT INTO ranks VALUES ('Technician','TECH1');
+
CREATE TABLE salary (
+
  rank VARCHAR(40),
+
  payment INTEGER);
+
INSERT INTO salary VALUES ('LECT1',2000);
+
INSERT INTO salary VALUES ('LECT2',3000);
+
INSERT INTO salary VALUES ('TECH1',5000);
+
INSERT INTO salary VALUES ('TECH2',6000);
+
</source>
+
<div>
+
SELECT payment FROM salary WHERE rank =
+
  (SELECT rank FROM ranks WHERE title =
+
  (SELECT title FROM jobs
+
    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.
+
<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 <code>FROM</code> clauses, use aliases</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>
+
</ul>
+
</div>
+
<source lang='sql' class='def'>
+
SELECT payment
+
  FROM salary, ranks, jobs
+
  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:
+
<pre>SELECT payment
+
  FROM salary JOIN ranks
+
    ON (salary.rank = ranks.rank)
+
  JOIN jobs ON (ranks.title = jobs.title)
+
  WHERE jobs.employee = 'Andrew Cumming'</pre></div>
+
</div>
+
{{Hacks Ref}}
+

Revision as of 11:42, 21 March 2014

SELECT cfu.* , ccd.*,count(*) as countt FROM (Select * from crm_follow_up ORDER BY follow_id DESC) as cfu left join crm_call_data as ccd

ON(cfu.sr_no=ccd.sr_no) WHERE ccd.assignto='neha' and ccd.data_mode=0 
GROUP BY ccd.name ORDER BY cfu.follow_id DESC
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense