Difference between revisions of "Subquery and JOIN"

From SQLZOO
Jump to: navigation, search
(Created page with "Converting subqueries into Join functions. <div class='ht'> <div class=params>schema:scott</div> <source lang=sql class='tidy'></source> <source lang=sql class='setup'> CREATE...")
 
Line 26: Line 26:
 
  SELECT payment FROM salary WHERE rank =
 
  SELECT payment FROM salary WHERE rank =
 
  (SELECT rank FROM ranks WHERE title =
 
  (SELECT rank FROM ranks WHERE title =
  (SELECT title FROM jobs WHERE employee = 'Andrew Cumming'))
+
  (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
 
This subquery would be more efficient if it was changed to a JOIN as there are no
 
aggregate functions in the queries.  
 
aggregate functions in the queries.  

Revision as of 09:04, 30 July 2012

Converting subqueries into Join functions.

schema:scott
 
 CREATE TABLE jobs(
  employee VARCHAR(40)),
  title VARCHAR(40));
INSERT INTO jobs VALUES ('Gordon Russel','Lecturer');
INSERT INTO jobs VALUES ('Andrew Cumming','Teaching fellow');
INSERT INTO jobs VALUES ('Jim Smith','Technician');
CREATE TABLE ranks (
   title VARCHAR(20),
   rank VARCHAR(20));
INSERT INTO city VALUES ('Lecturer','LECT1');
INSERT INTO city VALUES ('Teaching fellow','LECT2');
INSERT INTO city VALUES ('Technician','TECH1');
CREATE TABLE salary (
   rank VARCHAR(20),
   payment INTEGER);
INSERT INTO city VALUES ('LECT1',2000.00);
INSERT INTO city VALUES ('LECT2',3000.00);
INSERT INTO city VALUES ('TECH1',5000.00);
INSERT INTO city VALUES ('TECH2',6000.00 );
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'

{Hacks Ref}

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense