Difference between revisions of "Subquery and JOIN"

From SQLZOO
Jump to: navigation, search
Line 34: Line 34:
 
<ul>Use the following steps to change a subquery into a <code>JOIN</code>
 
<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>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>Delete all occurrences of (<code>SELECT</code> </li>
 
  <li>Substitute <code>WHERE</code> for AND after the first occurence of <code>WHERE</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 ranks.title = jobs.title
 
AND jobs.employee = 'Andrew Cumming'</source>
 
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  
 
SELECT payment  
 
FROM salary JOIN ranks ON (salary.rank = ranks.rank)
 
FROM salary JOIN ranks ON (salary.rank = ranks.rank)

Revision as of 14:34, 30 July 2012

Converting subqueries into Join functions.

schema:scott
DROP TABLE jobs;
DROP TABLE ranks;
DROP TABLE salary;
 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);
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 JOIN as there are no aggregate functions in the queries.

    Use the following steps to change a subquery into a JOIN
  • Mark all columns with the table name they came from
  • If you use the same table in two different FROM clauses, use aliases
  • Move all FROM statements together to form a single FROM
  • Delete all occurrences of (SELECT
  • Substitute WHERE for AND after the first occurence of WHERE
SELECT payment FROM salary, ranks, jobs
WHERE salary.rank = ranks.rank
AND ranks.title = jobs.title
AND jobs.employee = 'Andrew Cumming'
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense