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